package com.ocom.xfrecode.service.impl;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.PageHelper;
import com.ocom.common.entity.card.EntityXfMealInfo;
import com.ocom.common.entity.card.EntityXfRecord;
import com.ocom.common.enums.ResultEnum;
import com.ocom.common.request.web.*;
import com.ocom.common.request.xfrecde.*;
import com.ocom.common.resp.Result;
import com.ocom.common.utils.CommonUtil;
import com.ocom.common.utils.DateUtils;
import com.ocom.common.utils.ResultUtil;
import com.ocom.common.utils.StringUtils;
import com.ocom.common.vo.recode.ConRecCountVo;
import com.ocom.common.vo.xfrecode.*;
import com.ocom.xfrecode.mapper.XfRecordMapper;
import com.ocom.xfrecode.service.XfRecordService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Slf4j
@Service
public class XfRecordServiceImpl extends ServiceImpl<XfRecordMapper, EntityXfRecord> implements XfRecordService {

    @Autowired
    private XfRecordMapper xfRecordMapper;

    @Override
    public Boolean sveXfRecord(EntityXfRecord entityXfRecord) {

        return this.save(entityXfRecord);
    }


    @Override
    public Boolean deleteXfRecord(EntityXfRecord entityXfRecord) {
        QueryWrapper<EntityXfRecord> queryWrapper=new QueryWrapper<>();
        queryWrapper.eq("order_no",entityXfRecord.getOrderNo());
        queryWrapper.eq("device_no",entityXfRecord.getDeviceNo());
        queryWrapper.eq("month_value",entityXfRecord.getMonthValue());
        return this.remove(queryWrapper);
    }

    @Override
    public Boolean updateXfRecord(EntityXfRecord entityXfRecord) {
        UpdateWrapper<EntityXfRecord> queryWrapper=Wrappers.update();
        queryWrapper.eq("com_id",entityXfRecord.getComId());
        queryWrapper.eq("order_no",entityXfRecord.getOrderNo());
        if (org.apache.commons.lang3.StringUtils.isNotBlank(entityXfRecord.getDeviceNo())){
            queryWrapper.eq("device_no",entityXfRecord.getDeviceNo());
        }
        queryWrapper.eq("month_value",entityXfRecord.getMonthValue());
        if(entityXfRecord.getRefundTime()!=null){
            queryWrapper.set("refund_time",entityXfRecord.getRefundTime());
        }
        if(!CommonUtil.isNull(entityXfRecord.getReserved2())){
            queryWrapper.set("reserved2",entityXfRecord.getReserved2());
        }
        queryWrapper.set("if_refund",entityXfRecord.getIfRefund());
        if(entityXfRecord.getPayState()!= null && entityXfRecord.getPayState()>0) {
            queryWrapper.set("pay_state",entityXfRecord.getPayState());
        }
        if(entityXfRecord.getConsState()!= null && entityXfRecord.getConsState()>0) {
            queryWrapper.set("cons_state",entityXfRecord.getConsState());
        }
        if (null != entityXfRecord.getChannelFee()) {
            queryWrapper.set("channel_fee", entityXfRecord.getChannelFee());
        }
        if (null != entityXfRecord.getChannelDiscount()) {
            queryWrapper.set("channel_discount", entityXfRecord.getChannelDiscount());
        }
        return this.update(queryWrapper);
    }

    @Override
    public Boolean updateFace(EntityXfRecord entityXfRecord) {
        UpdateWrapper<EntityXfRecord> queryWrapper=Wrappers.update();
        queryWrapper.eq("com_id",entityXfRecord.getComId());
//        queryWrapper.eq("order_no",entityXfRecord.getOrderNo());
        queryWrapper.and(wrapper -> wrapper.eq("order_no", entityXfRecord.getOrderNo())
                .or().eq("order_no", "DK"+entityXfRecord.getOrderNo())
                .or().eq("order_no", "CZ"+entityXfRecord.getOrderNo())
                .or().eq("order_no", "SB"+entityXfRecord.getOrderNo())
                .or().eq("order_no", "LX"+entityXfRecord.getOrderNo()));
        queryWrapper.eq("device_no",entityXfRecord.getDeviceNo());
        queryWrapper.eq("month_value",entityXfRecord.getMonthValue());
        if(!CommonUtil.isNull(entityXfRecord.getFace())){
            queryWrapper.set("face",entityXfRecord.getFace());
        }
        if(entityXfRecord.getAdminId()!=null && entityXfRecord.getAdminId()>0){
            queryWrapper.set("admin_id",entityXfRecord.getIfRefund());
        }
        return this.update(queryWrapper);
    }

    @Override
    public Page<EntityXfRecord> webSelectDetails(JSONObject jsonObject) {

        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("com_id",jsonObject.getLong("comId"));
        if (jsonObject.getLong("merchId")!=null && jsonObject.getLong("merchId")!=-1){
            queryWrapper.eq("merch_id",jsonObject.getLong("merchId"));
        }
        if (!CommonUtil.isNull(jsonObject.getString("merchIds"))) {
            List<Integer> merchIds = Arrays.asList(jsonObject.getString("merchIds").split(",")).stream().map(x -> Integer.parseInt(x)).collect(Collectors.toList());
            queryWrapper.in("merch_id", merchIds);
        }
        if (jsonObject.getLong("pointId")!=null && jsonObject.getLong("pointId")!=-1){
            queryWrapper.eq("point_id",jsonObject.getLong("pointId"));
        }
        if (jsonObject.getString("deviceNo")!=null){
            queryWrapper.like("device_no",jsonObject.getString("deviceNo"));
        }
        if (jsonObject.getLong("pId")!=null){
            queryWrapper.eq("p_id",jsonObject.getLong("pId"));
        }
        if (jsonObject.getInteger("ifRefund")!=null && jsonObject.getInteger("ifRefund")>=0 ){
            queryWrapper.eq("if_refund",jsonObject.getInteger("ifRefund"));
        }
        if (jsonObject.getJSONArray("consStates")!=null && jsonObject.getJSONArray("consStates").size()>0){
            queryWrapper.in("cons_state",jsonObject.getJSONArray("consStates"));
        }
        if (jsonObject.getJSONArray("deptIds")!=null && jsonObject.getJSONArray("deptIds").size()>0){
            queryWrapper.in("dept_id",jsonObject.getJSONArray("deptIds"));
        }
        if (jsonObject.getString("pName")!=null){
            queryWrapper.like("p_name",jsonObject.getString("pName"));
        }
        if (jsonObject.getString("accountNo")!=null){
            queryWrapper.like("account_no",jsonObject.getString("accountNo"));
        }
        if (jsonObject.getString("channelOrderNo")!=null){
            queryWrapper.like("channel_order_no",jsonObject.getString("channelOrderNo"));
        }
        if (jsonObject.getString("orderNo")!=null){
            queryWrapper.like("order_no",jsonObject.getString("orderNo"));
        }
        if (jsonObject.getString("pCode")!=null){
            queryWrapper.like("p_code",jsonObject.getString("pCode"));
        }
//        if (jsonObject.getString("cardNo")!=null){
//            queryWrapper.like("card_no",jsonObject.getString("cardNo"));
//        }
        if (jsonObject.getInteger("consState")!=null && jsonObject.getInteger("consState")!= -1){
            queryWrapper.eq("cons_state",jsonObject.getInteger("consState"));
        }
        if (jsonObject.getInteger("deptId")!=null){
            queryWrapper.eq("dept_id",jsonObject.getInteger("deptId"));
        }
        if (jsonObject.getInteger("accTypeId")!=null && jsonObject.getInteger("accTypeId")>0){
            queryWrapper.eq("acc_type_id",jsonObject.getInteger("accTypeId"));
        }
        if (jsonObject.getInteger("mealNo")!=null && jsonObject.getInteger("mealNo")>0){
            queryWrapper.eq("meal_no",jsonObject.getInteger("mealNo"));
        }
        if(jsonObject.getLong("id") != null && jsonObject.getLong("id") > 0L){
            queryWrapper.ge("id",jsonObject.getLong("id"));
        }

        if(ObjectUtil.isNotEmpty(jsonObject.getInteger("receiptPrice"))){
            if(jsonObject.getInteger("receiptPrice").equals(0)){
                queryWrapper.eq("receipt_price",0);
            } else if (jsonObject.getInteger("receiptPrice").equals(1)) {
                queryWrapper.lt("receipt_price",0);
            }

        }

        if(ObjectUtil.isNotEmpty(jsonObject.getString("mealDate"))){
            String date = DateUtil.formatDate(DateUtils.strToDate2(jsonObject.getString("mealDate")));
            queryWrapper.eq("meal_date",date);
        }
        queryWrapper.lt("pay_state",100);
        if(ObjectUtil.isNotEmpty(jsonObject.getString("orderBy")) && jsonObject.getString("orderBy").equals("id")){
            queryWrapper.orderByAsc("id");
        }else{
            queryWrapper.orderByDesc("cons_time");
        }

        //消费
        if (StringUtils.isNotEmpty(jsonObject.getString("startTime")) && StringUtils.isNotEmpty(jsonObject.getString("endTime"))){
            String start = jsonObject.getString("startTime").substring(0, 7).replace("-", "");
            String end = jsonObject.getString("endTime").substring(0, 7).replace("-", "");
            List<String> dates = new ArrayList<>();
            try {
                dates = DateUtils.getMonthBetween(start, end);
            } catch (Exception e) {
                log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",jsonObject,e);
            }
            //小于等于
            queryWrapper.le("cons_time", DateUtils.strToDate(jsonObject.getString("endTime")));
            //大于等于
            queryWrapper.ge("cons_time", DateUtils.strToDate(jsonObject.getString("startTime")));

            List<String> finalDates = dates;
            finalDates.add(DateUtils.getMaxMonthDate(end));
            //日期范围查询
            queryWrapper.and(wrapper -> {
                for (String info : finalDates) {
                    wrapper.or().eq("month_value", info);
                }
            });
        }else {
            String end = DateUtils.getStrDate(new Date(),"yyyy-MM-dd HH:mm:ss").substring(0, 7).replace("-", "");
            queryWrapper.eq("month_value",end);
        }
        Page<EntityXfRecord> page = new Page<>(jsonObject.getInteger("page"), jsonObject.getInteger("size"));
        return xfRecordMapper.selectPage(page,queryWrapper);
    }

    @Override
    public List<EntityXfRecord> getConsumeReports(JSONObject jsonObject) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        List<EntityXfMealInfo> list=jsonObject.getObject("mealNameInfos",List.class);

        String merchIds =jsonObject.getString("merchIds");
        int indexs=0;
        String[] str=new String[list.size()*2+11];
        String[] arrMealNo=new String[list.size()];
        for (int i = 0; i < list.size(); i++) {
            EntityXfMealInfo entityXfMealInfo= JSON.parseObject(JSON.toJSONString(list.get(i)),EntityXfMealInfo.class) ;
            if (StringUtils.isNotEmpty(entityXfMealInfo.getMealNo())){
                str[indexs]="sum(case when meal_no='"+entityXfMealInfo.getMealNo()+"' and cons_state=1 then cons_money else 0 end) AS amount"+entityXfMealInfo.getMealNo();
                indexs=indexs+1;
                str[indexs]="sum(case when meal_no='"+entityXfMealInfo.getMealNo()+"' and cons_state=1 then 1 else 0 end) AS times"+entityXfMealInfo.getMealNo();
                arrMealNo[i]=entityXfMealInfo.getMealNo();
                indexs=indexs+1;
            }
        }
        String strMealNo= StringUtils.join(arrMealNo, ",");
        if (StringUtils.isNotEmpty(strMealNo)){
            str[indexs]="sum(case when meal_no not in ("+strMealNo+") and cons_state=1 then cons_money else 0 end) AS amountother";
            str[indexs+1]="sum(case when meal_no not in ("+strMealNo+") and cons_state=1 then 1 else 0 end) AS timesother";
            indexs=indexs+1;
        }
        str[indexs+1]="sum(case when cons_state=1 then cons_money else 0 end) AS allAmount";
        str[indexs+2]="sum(case when cons_state=1 then 1 else 0 end) AS allTimes";
        str[indexs+3]="sum(case when cons_state=2  then cons_money else 0 end) AS allRefund";
        str[indexs+4]="sum(case when cons_state=2  then 1 else 0 end) AS allRefundTimes";

        queryWrapper.eq("com_id",jsonObject.getLong("comId"));
        if(!CommonUtil.isNull(merchIds)){
            List<Integer> lis = new ArrayList<Integer>();
            List<String> list34 = Arrays.asList(merchIds.split(","));
            for (int i = 0; i < list34.size(); i++) {
                lis.add(Integer.valueOf(list34.get(i)));
            }
            queryWrapper.in("merch_id",list34);
        }else{
            if (jsonObject.getLong("merchId")!=null && jsonObject.getLong("merchId")!=-1){
                queryWrapper.eq("merch_id",jsonObject.getLong("merchId"));
            }
        }
        if (jsonObject.getLong("pointId")!=null && jsonObject.getLong("pointId")!=-1){
            queryWrapper.eq("point_id",jsonObject.getLong("pointId"));
        }
        queryWrapper.in("cons_state",1,2);
        if (jsonObject.getString("deviceNo")!=null){
            queryWrapper.eq("device_no",jsonObject.getString("deviceNo"));
        }
        try {
            queryWrapper.between("create_time",DateUtils.getDate(jsonObject.getString("startTime"),DateUtils.YYYY_MM_DD_HH_MM_SS),DateUtils.getDate(jsonObject.getString("endTime"),DateUtils.YYYY_MM_DD_HH_MM_SS));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //消费
        if (StringUtils.isNotEmpty(jsonObject.getString("startTime")) && StringUtils.isNotEmpty(jsonObject.getString("endTime"))){
            String start = jsonObject.getString("startTime").substring(0, 7).replace("-", "");
            String end = jsonObject.getString("endTime").substring(0, 7).replace("-", "");
            List<String> dates = new ArrayList<>();
            try {
                dates = DateUtils.getMonthBetween(start, end);
            } catch (Exception e) {
                log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",jsonObject,e);
            }

            List<String> finalDates = dates;
            finalDates.add(DateUtils.getMaxMonthDate(end));
            //日期范围查询
            queryWrapper.and(wrapper -> {
                for (String info : finalDates) {
                    wrapper.or().eq("month_value", info);
                }
            });
        }else {
            String end = DateUtils.getStrDate(new Date(),"yyyy-MM-dd HH:mm:ss").substring(0, 7).replace("-", "");
            queryWrapper.eq("month_value",end);
        }


        String groupByStr = "GROUP BY merch_id,point_id,device_no";
        if(jsonObject.getInteger("groupbyLv")!=null){
            if (jsonObject.getInteger("groupbyLv") == 1) {
                groupByStr = "GROUP BY merch_id,point_id";
            }
            if (jsonObject.getInteger("groupbyLv") == 2) {
                groupByStr = "GROUP BY merch_id";
            }
            if (jsonObject.getInteger("groupbyLv") == 3) {
                groupByStr = "";
            }
        }

        if (jsonObject.getInteger("reportType")==1){
            str[indexs+5]="DATE_FORMAT(create_time, '%Y-%m-%d')  AS  sTime";
            queryWrapper.last(groupByStr+",DATE_FORMAT(create_time, '%Y-%m-%d') order by  merch_id,point_id,device_no, create_time desc");
        }else if (jsonObject.getInteger("reportType")==2){
            str[indexs+5]="DATE_FORMAT(create_time, '%Y-%m')  AS  sTime";
            queryWrapper.last(groupByStr+",MONTH(create_time) order by  merch_id,point_id,device_no, cons_time desc");
        }else {
            str[indexs+5]="CONCAT('"+jsonObject.getString("startTime")+"', '~','"+jsonObject.getString("endTime")+"')  AS  sTime";
            queryWrapper.last(groupByStr+" order by  merch_id,point_id,device_no, create_time desc");
        }
        str[indexs+6]="device_no";
        str[indexs+7]="point_name";
        str[indexs+8]="merch_name";
        str[indexs+9]="device_name";
        queryWrapper.select(str);
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> getConsumeReportsC(JSONObject jsonObject) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        List<EntityXfMealInfo> list=jsonObject.getObject("mealNameInfos",List.class);

        String merchIds =jsonObject.getString("merchIds");
        int indexs=0;
        String[] str=new String[list.size()*2+12];
        String[] arrMealNo=new String[list.size()];
        for (int i = 0; i < list.size(); i++) {
            EntityXfMealInfo entityXfMealInfo= JSON.parseObject(JSON.toJSONString(list.get(i)),EntityXfMealInfo.class) ;
            if (StringUtils.isNotEmpty(entityXfMealInfo.getMealNo())){
                str[indexs]="sum(case when meal_no='"+entityXfMealInfo.getMealNo()+"' and cons_state in (1,3,5) then cons_money else 0 end) AS amount"+entityXfMealInfo.getMealNo();
                indexs=indexs+1;
                str[indexs]="sum(case when meal_no='"+entityXfMealInfo.getMealNo()+"' and cons_state in (1,3,5) then 1 else 0 end) AS times"+entityXfMealInfo.getMealNo();
                arrMealNo[i]=entityXfMealInfo.getMealNo();
                indexs=indexs+1;
            }
        }
        String strMealNo= StringUtils.join(arrMealNo, ",");
        if (StringUtils.isNotEmpty(strMealNo)){
            str[indexs]="sum(case when meal_no not in ("+strMealNo+") and cons_state=1 then cons_money else 0 end) AS amountother";
            str[indexs+1]="sum(case when meal_no not in ("+strMealNo+") and cons_state=1 then 1 else 0 end) AS timesother";
            indexs=indexs+1;
        }
        str[indexs+1]="sum(case when cons_state in (1,3,5) then cons_money else 0 end) AS allAmount";
        str[indexs+2]="sum(case when cons_state in (1,3,5) then 1 else 0 end) AS allTimes";
        str[indexs+3]="sum(case when cons_state in (2,6)   then cons_money else 0 end) AS allRefund";
        str[indexs+4]="sum(case when cons_state in (2,6)  then 1 else 0 end) AS allRefundTimes";

        queryWrapper.eq("com_id",jsonObject.getLong("comId"));
        if(!CommonUtil.isNull(merchIds)){
            List<Integer> lis = new ArrayList<Integer>();
            List<String> list34 = Arrays.asList(merchIds.split(","));
            for (int i = 0; i < list34.size(); i++) {
                lis.add(Integer.valueOf(list34.get(i)));
            }
            queryWrapper.in("merch_id",list34);
        }else{
            if (jsonObject.getLong("merchId")!=null && jsonObject.getLong("merchId")!=-1){
                queryWrapper.eq("merch_id",jsonObject.getLong("merchId"));
            }
        }
        if (jsonObject.getLong("pointId")!=null && jsonObject.getLong("pointId")!=-1){
            queryWrapper.eq("point_id",jsonObject.getLong("pointId"));
        }
        queryWrapper.in("cons_state",1,2,3,5,6);
        if (jsonObject.getString("deviceNo")!=null){
            queryWrapper.eq("device_no",jsonObject.getString("deviceNo"));
        }
        try {
            queryWrapper.between("create_time",DateUtils.getDate(jsonObject.getString("startTime"),DateUtils.YYYY_MM_DD_HH_MM_SS),DateUtils.getDate(jsonObject.getString("endTime"),DateUtils.YYYY_MM_DD_HH_MM_SS));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //消费
        if (StringUtils.isNotEmpty(jsonObject.getString("startTime")) && StringUtils.isNotEmpty(jsonObject.getString("endTime"))){
            String start = jsonObject.getString("startTime").substring(0, 7).replace("-", "");
            String end = jsonObject.getString("endTime").substring(0, 7).replace("-", "");
            List<String> dates = new ArrayList<>();
            try {
                dates = DateUtils.getMonthBetween(start, end);
            } catch (Exception e) {
                log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",jsonObject,e);
            }

            List<String> finalDates = dates;
            finalDates.add(DateUtils.getMaxMonthDate(end));
            //日期范围查询
            queryWrapper.and(wrapper -> {
                for (String info : finalDates) {
                    wrapper.or().eq("month_value", info);
                }
            });
        }else {
            String end = DateUtils.getStrDate(new Date(),"yyyy-MM-dd HH:mm:ss").substring(0, 7).replace("-", "");
            queryWrapper.eq("month_value",end);
        }


        String groupByStr = "GROUP BY merch_id,point_id,device_no";
        if(jsonObject.getInteger("groupbyLv")!=null){
            if (jsonObject.getInteger("groupbyLv") == 1) {
                groupByStr = "GROUP BY merch_id,point_id";
            }
            if (jsonObject.getInteger("groupbyLv") == 2) {
                groupByStr = "GROUP BY merch_id";
            }
            if (jsonObject.getInteger("groupbyLv") == 3) {
                groupByStr = "";
            }
        }

        if (jsonObject.getInteger("reportType")==1){
            str[indexs+5]="DATE_FORMAT(create_time, '%Y-%m-%d')  AS  sTime";
            queryWrapper.last(groupByStr+",DATE_FORMAT(create_time, '%Y-%m-%d') order by  merch_id,point_id,device_no, create_time desc");
        }else if (jsonObject.getInteger("reportType")==2){
            str[indexs+5]="DATE_FORMAT(create_time, '%Y-%m')  AS  sTime";
            queryWrapper.last(groupByStr+",MONTH(create_time) order by  merch_id,point_id,device_no, cons_time desc");
        }else {
            str[indexs+5]="CONCAT('"+jsonObject.getString("startTime")+"', '~','"+jsonObject.getString("endTime")+"')  AS  sTime";
            queryWrapper.last(groupByStr+" order by  merch_id,point_id,device_no, create_time desc");
        }
        str[indexs+6]="device_no";
        str[indexs+7]="point_name";
        str[indexs+8]="merch_name";
        str[indexs+9]="device_name";
        str[indexs+10]="sum(discount) AS allDiscount";
        queryWrapper.select(str);
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> getConsumeReports10(JSONObject jsonObject) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
//        List<EntityXfMealInfo> list= jsonObject.getObject("mealNameInfos",List.class);
        List<EntityXfMealInfo> list= new ArrayList<>();

        String merchIds =jsonObject.getString("merchIds");
        int indexs=0;
        String[] str=new String[list.size()*2+11];
        String[] arrMealNo=new String[list.size()];
//        for (int i = 0; i < list.size(); i++) {
//            EntityXfMealInfo entityXfMealInfo= JSON.parseObject(JSON.toJSONString(list.get(i)),EntityXfMealInfo.class) ;
//            if (StringUtils.isNotEmpty(entityXfMealInfo.getMealNo())){
//                str[indexs]="sum(case when meal_no='"+entityXfMealInfo.getMealNo()+"' and cons_state=1 then cons_money else 0 end) AS amount"+entityXfMealInfo.getMealNo();
//                indexs=indexs+1;
//                str[indexs]="sum(case when meal_no='"+entityXfMealInfo.getMealNo()+"' and cons_state=1 then 1 else 0 end) AS times"+entityXfMealInfo.getMealNo();
//                arrMealNo[i]=entityXfMealInfo.getMealNo();
//                indexs=indexs+1;
//            }
//        }
//        String strMealNo= StringUtils.join(arrMealNo, ",");
//        if (StringUtils.isNotEmpty(strMealNo)){
//            str[indexs]="sum(case when meal_no not in ("+strMealNo+") and cons_state=1 then cons_money else 0 end) AS amountother";
//            str[indexs+1]="sum(case when meal_no not in ("+strMealNo+") and cons_state=1 then 1 else 0 end) AS timesother";
//            indexs=indexs+1;
//        }
        str[indexs+1]="sum(case when cons_state=10 then cons_money else 0 end) AS allAmount";
        str[indexs+2]="sum(case when cons_state=10 then 1 else 0 end) AS allTimes";
        str[indexs+3]="sum(case when cons_state=11  then cons_money else 0 end) AS allRefund";
        str[indexs+4]="sum(case when cons_state=11  then 1 else 0 end) AS allRefundTimes";

        queryWrapper.eq("com_id",jsonObject.getLong("comId"));
        if(!CommonUtil.isNull(merchIds)){
            List<Integer> lis = new ArrayList<Integer>();
            List<String> list34 = Arrays.asList(merchIds.split(","));
            for (int i = 0; i < list34.size(); i++) {
                lis.add(Integer.valueOf(list34.get(i)));
            }
            queryWrapper.in("merch_id",list34);
        }else{
            if (jsonObject.getLong("merchId")!=null && jsonObject.getLong("merchId")!=-1){
                queryWrapper.eq("merch_id",jsonObject.getLong("merchId"));
            }
        }
        if (jsonObject.getLong("pointId")!=null && jsonObject.getLong("pointId")!=-1){
            queryWrapper.eq("point_id",jsonObject.getLong("pointId"));
        }
        queryWrapper.in("cons_state",10,11);
        if (jsonObject.getString("deviceNo")!=null){
            queryWrapper.eq("device_no",jsonObject.getString("deviceNo"));
        }
        try {
            queryWrapper.between("create_time",DateUtils.getDate(jsonObject.getString("startTime"),DateUtils.YYYY_MM_DD_HH_MM_SS),DateUtils.getDate(jsonObject.getString("endTime"),DateUtils.YYYY_MM_DD_HH_MM_SS));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //消费
        if (StringUtils.isNotEmpty(jsonObject.getString("startTime")) && StringUtils.isNotEmpty(jsonObject.getString("endTime"))){
            String start = jsonObject.getString("startTime").substring(0, 7).replace("-", "");
            String end = jsonObject.getString("endTime").substring(0, 7).replace("-", "");
            List<String> dates = new ArrayList<>();
            try {
                dates = DateUtils.getMonthBetween(start, end);
            } catch (Exception e) {
                log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",jsonObject,e);
            }

            List<String> finalDates = dates;
            finalDates.add(DateUtils.getMaxMonthDate(end));
            //日期范围查询
            queryWrapper.and(wrapper -> {
                for (String info : finalDates) {
                    wrapper.or().eq("month_value", info);
                }
            });
        }else {
            String end = DateUtils.getStrDate(new Date(),"yyyy-MM-dd HH:mm:ss").substring(0, 7).replace("-", "");
            queryWrapper.eq("month_value",end);
        }


        String groupByStr = "GROUP BY merch_id,point_id,device_no";
        if(jsonObject.getInteger("groupbyLv")!=null){
            if (jsonObject.getInteger("groupbyLv") == 1) {
                groupByStr = "GROUP BY merch_id,point_id";
            }
            if (jsonObject.getInteger("groupbyLv") == 2) {
                groupByStr = "GROUP BY merch_id";
            }
            if (jsonObject.getInteger("groupbyLv") == 3) {
                groupByStr = "";
            }
        }

        if (jsonObject.getInteger("reportType")==1){
            str[indexs+5]="DATE_FORMAT(create_time, '%Y-%m-%d')  AS  sTime";
            queryWrapper.last(groupByStr+",DATE_FORMAT(create_time, '%Y-%m-%d') order by  merch_id,point_id,device_no, create_time desc");
        }else if (jsonObject.getInteger("reportType")==2){
            str[indexs+5]="DATE_FORMAT(create_time, '%Y-%m')  AS  sTime";
            queryWrapper.last(groupByStr+",MONTH(create_time) order by  merch_id,point_id,device_no, cons_time desc");
        }else {
            str[indexs+5]="CONCAT('"+jsonObject.getString("startTime")+"', '~','"+jsonObject.getString("endTime")+"')  AS  sTime";
            queryWrapper.last(groupByStr+" order by  merch_id,point_id,device_no, create_time desc");
        }
        str[indexs+6]="device_no";
        str[indexs+7]="point_name";
        str[indexs+8]="merch_name";
        str[indexs+9]="device_name";
        queryWrapper.select(str);
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> getConsumeReports10C(JSONObject jsonObject) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
//        List<EntityXfMealInfo> list= jsonObject.getObject("mealNameInfos",List.class);
        List<EntityXfMealInfo> list= new ArrayList<>();

        String merchIds =jsonObject.getString("merchIds");
        int indexs=0;
        String[] str=new String[list.size()*2+11];
        String[] arrMealNo=new String[list.size()];
//        for (int i = 0; i < list.size(); i++) {
//            EntityXfMealInfo entityXfMealInfo= JSON.parseObject(JSON.toJSONString(list.get(i)),EntityXfMealInfo.class) ;
//            if (StringUtils.isNotEmpty(entityXfMealInfo.getMealNo())){
//                str[indexs]="sum(case when meal_no='"+entityXfMealInfo.getMealNo()+"' and cons_state=1 then cons_money else 0 end) AS amount"+entityXfMealInfo.getMealNo();
//                indexs=indexs+1;
//                str[indexs]="sum(case when meal_no='"+entityXfMealInfo.getMealNo()+"' and cons_state=1 then 1 else 0 end) AS times"+entityXfMealInfo.getMealNo();
//                arrMealNo[i]=entityXfMealInfo.getMealNo();
//                indexs=indexs+1;
//            }
//        }
//        String strMealNo= StringUtils.join(arrMealNo, ",");
//        if (StringUtils.isNotEmpty(strMealNo)){
//            str[indexs]="sum(case when meal_no not in ("+strMealNo+") and cons_state=1 then cons_money else 0 end) AS amountother";
//            str[indexs+1]="sum(case when meal_no not in ("+strMealNo+") and cons_state=1 then 1 else 0 end) AS timesother";
//            indexs=indexs+1;
//        }
        str[indexs+1]="sum(case when cons_state=10 then cons_money else 0 end) AS allAmount";
        str[indexs+2]="sum(case when cons_state=10 then 1 else 0 end) AS allTimes";
        str[indexs+3]="sum(case when cons_state=11  then cons_money else 0 end) AS allRefund";
        str[indexs+4]="sum(case when cons_state=11  then 1 else 0 end) AS allRefundTimes";

        queryWrapper.eq("com_id",jsonObject.getLong("comId"));
        if(!CommonUtil.isNull(merchIds)){
            List<Integer> lis = new ArrayList<Integer>();
            List<String> list34 = Arrays.asList(merchIds.split(","));
            for (int i = 0; i < list34.size(); i++) {
                lis.add(Integer.valueOf(list34.get(i)));
            }
            queryWrapper.in("merch_id",list34);
        }else{
            if (jsonObject.getLong("merchId")!=null && jsonObject.getLong("merchId")!=-1){
                queryWrapper.eq("merch_id",jsonObject.getLong("merchId"));
            }
        }
        if (jsonObject.getLong("pointId")!=null && jsonObject.getLong("pointId")!=-1){
            queryWrapper.eq("point_id",jsonObject.getLong("pointId"));
        }
        queryWrapper.in("cons_state",10,11);
        if (jsonObject.getString("deviceNo")!=null){
            queryWrapper.eq("device_no",jsonObject.getString("deviceNo"));
        }
        try {
            queryWrapper.between("create_time",DateUtils.getDate(jsonObject.getString("startTime"),DateUtils.YYYY_MM_DD_HH_MM_SS),DateUtils.getDate(jsonObject.getString("endTime"),DateUtils.YYYY_MM_DD_HH_MM_SS));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //消费
        if (StringUtils.isNotEmpty(jsonObject.getString("startTime")) && StringUtils.isNotEmpty(jsonObject.getString("endTime"))){
            String start = jsonObject.getString("startTime").substring(0, 7).replace("-", "");
            String end = jsonObject.getString("endTime").substring(0, 7).replace("-", "");
            List<String> dates = new ArrayList<>();
            try {
                dates = DateUtils.getMonthBetween(start, end);
            } catch (Exception e) {
                log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",jsonObject,e);
            }

            List<String> finalDates = dates;
            finalDates.add(DateUtils.getMaxMonthDate(end));
            //日期范围查询
            queryWrapper.and(wrapper -> {
                for (String info : finalDates) {
                    wrapper.or().eq("month_value", info);
                }
            });
        }else {
            String end = DateUtils.getStrDate(new Date(),"yyyy-MM-dd HH:mm:ss").substring(0, 7).replace("-", "");
            queryWrapper.eq("month_value",end);
        }


        String groupByStr = "GROUP BY merch_id,point_id,device_no";
        if(jsonObject.getInteger("groupbyLv")!=null){
            if (jsonObject.getInteger("groupbyLv") == 1) {
                groupByStr = "GROUP BY merch_id,point_id";
            }
            if (jsonObject.getInteger("groupbyLv") == 2) {
                groupByStr = "GROUP BY merch_id";
            }
            if (jsonObject.getInteger("groupbyLv") == 3) {
                groupByStr = "";
            }
        }

        if (jsonObject.getInteger("reportType")==1){
            str[indexs+5]="DATE_FORMAT(create_time, '%Y-%m-%d')  AS  sTime";
            queryWrapper.last(groupByStr+",DATE_FORMAT(create_time, '%Y-%m-%d') order by  merch_id,point_id,device_no, create_time desc");
        }else if (jsonObject.getInteger("reportType")==2){
            str[indexs+5]="DATE_FORMAT(create_time, '%Y-%m')  AS  sTime";
            queryWrapper.last(groupByStr+",MONTH(create_time) order by  merch_id,point_id,device_no, cons_time desc");
        }else {
            str[indexs+5]="CONCAT('"+jsonObject.getString("startTime")+"', '~','"+jsonObject.getString("endTime")+"')  AS  sTime";
            queryWrapper.last(groupByStr+" order by  merch_id,point_id,device_no, create_time desc");
        }
        str[indexs+6]="device_no";
        str[indexs+7]="point_name";
        str[indexs+8]="merch_name";
        str[indexs+9]="device_name";
        queryWrapper.select(str);
        return xfRecordMapper.selectList(queryWrapper);
    }
    @Override
    public EntityXfRecord getxfUserparam(EntityXfRecord entityXfRecord) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("count(id) as times","sum(real_money) as amount");
        queryWrapper.eq("p_id",entityXfRecord.getPId());
        queryWrapper.eq("com_id",entityXfRecord.getComId());
//        queryWrapper.eq("cons_state",1);
        queryWrapper.in("cons_state",1,3,5);
        queryWrapper.eq("pay_state",1);
        queryWrapper.eq("if_refund",0);
        queryWrapper.eq("cons_type",entityXfRecord.getConsType());

        if(entityXfRecord.getReserved1()!=null && entityXfRecord.getReserved1().equals("selectMerchTrue")){
            queryWrapper.eq("merch_id",entityXfRecord.getMerchId());
        }

//        String strDate=DateUtils.getStrDate(new Date(),DateUtils.YYYY_MM_DD_HH_MM_SS);
//        String start = strDate.substring(0, 7).replace("-", "");
//        List<String> dates = new ArrayList<>();
//        try {
//            dates = DateUtils.getMonthBetween(start, start);
//        } catch (Exception e) {
//            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",entityXfRecord,e);
//        }
//
//        List<String> finalDates = dates;
//        //日期范围查询
//        queryWrapper.and(wrapper -> {
//            for (String info : finalDates) {
//                wrapper.or().eq("month_value", info);
//            }
//        });

        String strDate = DateUtils.getStrDate(new Date(), DateUtils.YYYY_MM_DD_HH_MM_SS);
        if (entityXfRecord.getConsTime() != null) {
            strDate = DateUtils.getStrDate(entityXfRecord.getConsTime(), DateUtils.YYYY_MM_DD_HH_MM_SS);
        }

        String endMonth = strDate.substring(0,7).replace("-","");
        String startMonth = DateUtils.getLastMonth(endMonth, 1);

        List<String> dates = new ArrayList<>();
        dates.add(endMonth);
        dates.add(startMonth);

        if (entityXfRecord.getMealNo().equals("500")){
            //餐段按天
//            queryWrapper.last("and TO_DAYS(create_time) = TO_DAYS('"+strDate+"')");
            strDate = strDate.substring(0,10);
//            queryWrapper.between( "create_time",strDate+" 00:00:00",strDate+" 23:59:59");
            queryWrapper.eq("meal_date", strDate);
        }else if (entityXfRecord.getMealNo().equals("1000")){
            //按月查询
//            queryWrapper.last("and DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT('"+strDate+"','%Y%m')");
//            strDate = strDate.substring(0, 7).replace("-", "");
//            queryWrapper.apply(" DATE_FORMAT(meal_date, '%Y%m') =" + strDate);
            strDate = strDate.substring(0, 7);
            String endDate = DateUtils.getLastDayOfMonth(Integer.valueOf(strDate.substring(5)));
            queryWrapper.between("meal_date",strDate + "-01", endDate);
        }else {
            queryWrapper.eq("meal_no",entityXfRecord.getMealNo());
//            queryWrapper.last("and TO_DAYS(create_time) = TO_DAYS('"+strDate+"')");
            strDate = strDate.substring(0,10);
            queryWrapper.eq("meal_date", strDate);
//            queryWrapper.between( "create_time",strDate+" 00:00:00",strDate+" 23:59:59");
        }
        EntityXfRecord entityXfRecord1 = new EntityXfRecord();
        entityXfRecord1.setTimes(0);
        entityXfRecord1.setAmount(0l);
        for (String month : dates) {
            QueryWrapper<EntityXfRecord> queryWrapper2 = queryWrapper.clone();
            queryWrapper2.eq("month_value", month);
            EntityXfRecord entityXfRecords3 = xfRecordMapper.selectOne(queryWrapper2);
            entityXfRecord1.setTimes(entityXfRecord1.getTimes() + entityXfRecords3.getTimes());
            if(entityXfRecords3.getAmount()!=null) entityXfRecord1.setAmount(entityXfRecord1.getAmount() + entityXfRecords3.getAmount());
        }
        return entityXfRecord1;
//        return xfRecordMapper.selectOne(queryWrapper);
    }

    @Override
    public EntityXfRecord getxfUserparamDiscount(EntityXfRecord entityXfRecord) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("count(id) as times","sum(discount) as amount");
        queryWrapper.eq("p_id",entityXfRecord.getPId());
        queryWrapper.eq("com_id",entityXfRecord.getComId());
        queryWrapper.eq("cons_state",1);
        queryWrapper.eq("pay_state",1);
        queryWrapper.eq("if_refund",0);
        queryWrapper.gt("discount",0);
        queryWrapper.eq("cons_type",entityXfRecord.getConsType());

        if(entityXfRecord.getReserved1()!=null && entityXfRecord.getReserved1().equals("selectMerchTrue")){
            queryWrapper.eq("merch_id",entityXfRecord.getMerchId());
        }

//        String strDate=DateUtils.getStrDate(new Date(),DateUtils.YYYY_MM_DD_HH_MM_SS);
//        String start = strDate.substring(0, 7).replace("-", "");
//        List<String> dates = new ArrayList<>();
//        try {
//            dates = DateUtils.getMonthBetween(start, start);
//        } catch (Exception e) {
//            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",entityXfRecord,e);
//        }
//
//        List<String> finalDates = dates;
//        //日期范围查询
//        queryWrapper.and(wrapper -> {
//            for (String info : finalDates) {
//                wrapper.or().eq("month_value", info);
//            }
//        });
        String strDate = DateUtils.getStrDate(new Date(), DateUtils.YYYY_MM_DD_HH_MM_SS);
        if (entityXfRecord.getConsTime() != null) {
            strDate = DateUtils.getStrDate(entityXfRecord.getConsTime(), DateUtils.YYYY_MM_DD_HH_MM_SS);
        }

        String endMonth = strDate.substring(0,7).replace("-","");
        String startMonth = DateUtils.getLastMonth(endMonth, 1);

        List<String> dates = new ArrayList<>();
        dates.add(endMonth);
        dates.add(startMonth);

        if (entityXfRecord.getMealNo().equals("500")){
            //餐段按天
//            queryWrapper.last("and TO_DAYS(create_time) = TO_DAYS('"+strDate+"')");
            strDate = strDate.substring(0,10);
//            queryWrapper.between( "create_time",strDate+" 00:00:00",strDate+" 23:59:59");
            queryWrapper.eq("meal_date", strDate);
        }else if (entityXfRecord.getMealNo().equals("1000")){
            //按月查询
//            queryWrapper.last("and DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT('"+strDate+"','%Y%m')");
//            strDate = strDate.substring(0, 7).replace("-", "");
//            queryWrapper.apply(" DATE_FORMAT(meal_date, '%Y%m') =" + strDate);
            strDate = strDate.substring(0, 7);
            String endDate = DateUtils.getLastDayOfMonth(Integer.valueOf(strDate.substring(5)));
            queryWrapper.between("meal_date",strDate + "-01", endDate);
        }else {
            queryWrapper.eq("meal_no",entityXfRecord.getMealNo());
//            queryWrapper.last("and TO_DAYS(create_time) = TO_DAYS('"+strDate+"')");
            strDate = strDate.substring(0,10);
            queryWrapper.eq("meal_date", strDate);
//            queryWrapper.between( "create_time",strDate+" 00:00:00",strDate+" 23:59:59");
        }
        EntityXfRecord entityXfRecord1 = new EntityXfRecord();
        entityXfRecord1.setTimes(0);
        entityXfRecord1.setAmount(0l);
        for (String month : dates) {
            QueryWrapper<EntityXfRecord> queryWrapper2 = queryWrapper.clone();
            queryWrapper2.eq("month_value", month);
            EntityXfRecord entityXfRecords3 = xfRecordMapper.selectOne(queryWrapper2);
            entityXfRecord1.setTimes(entityXfRecord1.getTimes() + entityXfRecords3.getTimes());
            if(entityXfRecords3.getAmount()!=null) entityXfRecord1.setAmount(entityXfRecord1.getAmount() + entityXfRecords3.getAmount());
        }
        return entityXfRecord1;
    }

    @Override
    public EntityXfRecord getTimes(EntityXfRecord entityXfRecord) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("count(*) as times");
        queryWrapper.eq("p_id",entityXfRecord.getPId());
        queryWrapper.eq("com_id",entityXfRecord.getComId());
//        queryWrapper.eq("cons_state",1);
        queryWrapper.in("cons_state",1,3,5);
        queryWrapper.eq("pay_state",1);
        queryWrapper.eq("if_refund",0);
        queryWrapper.eq("cons_type",entityXfRecord.getConsType());

        if(!CommonUtil.isNull(entityXfRecord.getReserved1()) && entityXfRecord.getReserved1().equals("selectMerchTrue")){
            queryWrapper.eq("merch_id",entityXfRecord.getMerchId());
        }
        if (!CommonUtil.isNull(entityXfRecord.getReserved2())) {
            queryWrapper.apply("find_in_set(  meal_no , \""+entityXfRecord.getReserved2()+"\" )  and meal_no IS NOT NULL ");
        }

//        String strDate=DateUtils.getStrDate(entityXfRecord.getConsTime(),DateUtils.YYYY_MM_DD_HH_MM_SS);
//        String start = strDate.substring(0, 7).replace("-", "");
//        String end = DateUtils.getStrDate(new Date(),DateUtils.YYYY_MM_DD_HH_MM_SS).substring(0, 7).replace("-", "");
//        List<String> dates = new ArrayList<>();
//        try {
//            dates = DateUtils.getMonthBetween(start, end);
//        } catch (Exception e) {
//            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",entityXfRecord,e);
//        }
//
//        List<String> finalDates = dates;
//        //日期范围查询
//        queryWrapper.and(wrapper -> {
//            for (String info : finalDates) {
//                wrapper.or().eq("month_value", info);
//            }
//        });

        String strDate=DateUtils.getStrDate(entityXfRecord.getConsTime(),DateUtils.YYYY_MM_DD_HH_MM_SS);
        String endMonth = strDate.substring(0,7).replace("-","");
        String startMonth = DateUtils.getLastMonth(endMonth, 1);

        List<String> dates = new ArrayList<>();
        dates.add(endMonth);
        dates.add(startMonth);

        if (entityXfRecord.getMealNo().equals("500")){
            //餐段按天
            strDate = strDate.substring(0,10);
            queryWrapper.eq( "meal_date",strDate);
        }else if (entityXfRecord.getMealNo().equals("1000")){
            //按月查询
            //queryWrapper.last("and DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT('"+strDate+"','%Y%m')");
            strDate = strDate.substring(0, 7).replace("-", "");
            queryWrapper.eq("month_value", strDate);
        }else {
            if (CommonUtil.isNull(entityXfRecord.getReserved2())) {
                queryWrapper.eq("meal_no", entityXfRecord.getMealNo());
            }
            strDate = strDate.substring(0,10);
            queryWrapper.eq( "meal_date",strDate);
        }

        EntityXfRecord entityXfRecord1 = new EntityXfRecord();
        entityXfRecord1.setTimes(0);
        for (String month : dates) {
            QueryWrapper<EntityXfRecord> queryWrapper2 = queryWrapper.clone();
            queryWrapper2.eq("month_value", month);
            EntityXfRecord entityXfRecords3 = xfRecordMapper.selectOne(queryWrapper2);
            entityXfRecord1.setTimes(entityXfRecord1.getTimes() + entityXfRecords3.getTimes());
        }
        return entityXfRecord1;
    }

    @Override
    public List<EntityXfRecord> dayTotalMealNo(JSONObject jsonObject) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(" meal_date as sTime ",
                " case when cons_type>1 then  -1  when  meal_no='' or meal_no is null then '0' else meal_no end as meal_no",
                " sum(1) orderNum",
                " sum(real_money) amount");

//        String summaryDate = jsonObject.getString("summaryDate");
//        Date date = DateUtils.strToDate4(summaryDate);
//
//        String start = summaryDate.replace("-", "");
//        String end = summaryDate.replace("-", "");
//        List<String> dates = new ArrayList<>();
//        try {
//            dates = DateUtils.getMonthBetween(start, end);
//        } catch (Exception e) {
//            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每天各餐段汇总结果",jsonObject,e);
//        }
//
//        List<String> finalDates = dates;
//        //日期范围查询
//        queryWrapper.and(wrapper -> {
//            for (String info : finalDates) {
//                wrapper.or().eq("month_value", info);
//            }
//        });

        String summaryDate = jsonObject.getString("summaryDate");
        Date date = DateUtils.strToDate4(summaryDate);
        String start = summaryDate.replace("-", "");

        String lastMonth = summaryDate.substring(0,7).replace("-","");
        //同月往前查一个月
        lastMonth = DateUtils.getLastMonth(lastMonth, 1);

        List<String> dates = new ArrayList<>();
        dates.add(start);
        dates.add(lastMonth);

        queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
        if (jsonObject.getLong("merchID")!=-1){
            queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
        }
        queryWrapper.in("cons_state",1,3,5);
        queryWrapper.between("meal_date", DateUtils.getFirstDayOfMonth(date).toString(), DateUtils.getLastDayOfMonth(date).toString());
        queryWrapper.eq("if_refund",0);
        queryWrapper.groupBy("meal_date,case when  cons_type>1 then  -1  when  meal_no='' or meal_no is null  then '0' else meal_no end");
        queryWrapper.orderByAsc("meal_date","cons_type","meal_no");
   //     return xfRecordMapper.selectList(queryWrapper);


        List<EntityXfRecord> entityXfRecords = new ArrayList<>();
        for (String month : dates) {
            QueryWrapper<EntityXfRecord> queryWrapper2 = queryWrapper.clone();
            queryWrapper2.eq("month_value", month);
            List<EntityXfRecord> entityXfRecords3 = xfRecordMapper.selectList(queryWrapper2);
            if (entityXfRecords3 != null && entityXfRecords3.size() > 0) entityXfRecords.addAll(entityXfRecords3);
        }

        List<EntityXfRecord> entityXfRecords2 = new ArrayList<>();

        Map<String,Integer> stringGetPointMealPreVoMap =new HashMap<>();

        if (entityXfRecords != null && entityXfRecords.size() > 0) {
            for (EntityXfRecord entityXfRecord : entityXfRecords) {
                String key = "#meal_date:"+entityXfRecord.getSTime()+ "#meal_no:"+entityXfRecord.getMealNo();
                if(stringGetPointMealPreVoMap.containsKey(key)){
                    EntityXfRecord entityXfRecord3= entityXfRecords2.get(stringGetPointMealPreVoMap.get(key));
                    entityXfRecord.setOrderNum(entityXfRecord.getOrderNum()+entityXfRecord3.getOrderNum());
                    entityXfRecord.setAmount(entityXfRecord.getAmount()+entityXfRecord3.getAmount());
                    entityXfRecords2.set(stringGetPointMealPreVoMap.get(key),entityXfRecord);
                }else{
                    entityXfRecords2.add(entityXfRecord);
                    stringGetPointMealPreVoMap.put(key,entityXfRecords2.size()-1);
                }
            }
        }

        return entityXfRecords2;

    }

  @Override
  public List<EntityXfRecord> dayTotalMealNoXM(JSONObject jsonObject) {
    QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
    queryWrapper.select(" date(create_time) as sTime ",
        " case when  meal_no='' or meal_no is null then '0' else meal_no end as meal_no",
        " sum(case when  cons_state in  (2,6) then -1  else  1 end  ) orderNum",
        " sum(real_money) amount");

    String summaryDate = jsonObject.getString("summaryDate");
    Date date = DateUtils.strToDate4(summaryDate);

    String start = summaryDate.replace("-", "");
    String end = summaryDate.replace("-", "");
    List<String> dates = new ArrayList<>();
    try {
      dates = DateUtils.getMonthBetween(start, end);
    } catch (Exception e) {
      log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每天各餐段汇总结果",jsonObject,e);
    }

    List<String> finalDates = dates;
    //日期范围查询
    queryWrapper.and(wrapper -> {
      for (String info : finalDates) {
        wrapper.or().eq("month_value", info);
      }
    });
    queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
    if (jsonObject.getLong("merchID")!=-1){
      queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
    }
    queryWrapper.in("cons_state",1,3,5,2,6);
    /*queryWrapper.between(" date(create_time)", DateUtils.getFirstDayOfMonth(date).toString(),
        DateUtils.getLastDayOfMonth(date).toString());
    queryWrapper.eq("if_refund",0);*/
    queryWrapper.groupBy(" date(create_time), case when  meal_no='' or meal_no is null then '0' else meal_no end");
    queryWrapper.orderByAsc(" date(create_time)","cons_type","meal_no");
    return xfRecordMapper.selectList(queryWrapper);
  }

    @Override
    public List<EntityXfRecord> dayTotalMealNoCanteenNo(JSONObject jsonObject) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("DATE_FORMAT(cons_time, '%Y-%m-%d') sTime",
                "cons_type as reportType",
                "sum(case when  cons_state in (1,5)  then 1 else 0 end) orderNum",
                "sum(case when  cons_state in (1,5)  then real_money else 0 end) amount",
                "sum(case when  cons_state in (2,6) then  1 else 0 end) refundNum",
                "sum(case when  cons_state in (2,6) then  real_money else 0 end) refundAmount");

        String start = jsonObject.getString("summaryDate").replace("-", "");
        String end = jsonObject.getString("summaryDate").replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每天非饭堂各餐段汇总结果",jsonObject,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
        if (jsonObject.getLong("merchID")!=-1){
            queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
        }
        queryWrapper.gt("cons_type",1);
        queryWrapper.in("cons_state",1,2,5,6);
        queryWrapper.groupBy("DATE_FORMAT(cons_time, '%Y-%m-%d'),cons_type");
        queryWrapper.orderByAsc("DATE_FORMAT(cons_time, '%Y-%m-%d'),cons_type");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> monthMealNoCanteenNo(JSONObject jsonObject, String start, String end) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("DATE_FORMAT(cons_time, '%Y-%m') stime",
                "cons_type as reportType",
                "sum(case when  cons_state in (1,5)  then 1 else 0 end) orderNum",
                "sum(case when  cons_state in (1,5)  then real_money else 0 end) amount",
                "sum(case when  cons_state in (2,6) then  1 else 0 end) refundNum",
                "sum(case when  cons_state in (2,6) then  real_money else 0 end) refundAmount");

        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每月各餐段汇总结果",jsonObject,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
        if (jsonObject.getLong("merchID")!=-1){
            queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
        }
        queryWrapper.gt("cons_type",1);
        queryWrapper.in("cons_state",1,2,5,6);
        queryWrapper.groupBy("DATE_FORMAT(cons_time, '%Y-%m')","cons_type");
        queryWrapper.orderByAsc("DATE_FORMAT(cons_time, '%Y-%m')","cons_type");
        return xfRecordMapper.selectList(queryWrapper);
    }

    /**
     *@Description:钱包余额,查询充值钱包
     * @author lu 
     * @param comId
     * @param month 
     * @return java.util.List<com.ocom.common.entity.card.EntityXfRecord>
     * @date 2022/10/13 15:13       
     */
    @Override
    public List<EntityXfRecord> walletBalance(Long comId, String month,Integer types,Integer subAccount) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        if (types==1){
            queryWrapper.select("(SUBSTRING_INDEX(\n" +
                            "   \tGROUP_CONCAT(charge_balance ORDER BY create_time DESC,id DESC),\n" +
                            "   \t',',1\n" +
                            "   )) closeBalance","(SUBSTRING_INDEX(\n" +
                            "   \tGROUP_CONCAT(charge_balance-charge_money ORDER BY create_time,id ASC),\n" +
                            "   \t',',1\n" +
                            "   )) openBalance",
                    "account_no",
                    "SUM(if(cons_state<10,charge_money,0)) as consumeAmount",
                    "SUM(if(cons_state<10 and charge_money<>0,1,0)) as consumeNumber",
                    "SUM(if(cons_state in(10,11),charge_money,0)) as rechargeAmount",
                    "SUM(if(cons_state in(10,11) and charge_money<>0,1,0)) as rechargeNumber",
                    "SUM(if(cons_state=21,charge_money,0)) as settleAmount",
                    "SUM(if(cons_state=21,1,0)) as settleNumber");
        }else {
            queryWrapper.select("(SUBSTRING_INDEX(\n" +
                            "   \tGROUP_CONCAT(sub_balance ORDER BY create_time DESC,id DESC),\n" +
                            "   \t',',1\n" +
                            "   )) closeBalance","(SUBSTRING_INDEX(\n" +
                            "   \tGROUP_CONCAT(sub_balance-sub_money ORDER BY create_time,id ASC),\n" +
                            "   \t',',1\n" +
                            "   )) openBalance",
                    "account_no",
                    "SUM(if(cons_state<10,sub_money,0)) as consumeAmount",
                    "SUM(if(cons_state<10 and sub_money<>0,1,0)) as consumeNumber",
                    "SUM(if(cons_state in(10,12),sub_money,0)) as rechargeAmount",
                    "SUM(if(cons_state in(12) and sub_money<>0,1,0)) as rechargeNumber",
                    "SUM(if(cons_state=21,sub_money,0)) as settleAmount",
                    "SUM(if(cons_state=21,1,0)) as settleNumber");
        }

        String start = month.replace("-", "");
        String end = month.replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","钱包余额,查询充值钱包",month,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",comId);
        queryWrapper.eq("sub_account",subAccount);
        queryWrapper.isNotNull("account_no");
        queryWrapper.groupBy("account_no");
        //queryWrapper.lt("op_type_diff",200);
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> walletBalanceFirstDate(Long comId, Date yesterdayD, Date firstDateD, String month,Integer types,Integer subAccount) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        if (types==1){
            queryWrapper.select("(SUBSTRING_INDEX(\n" +
                            "   \tGROUP_CONCAT(charge_balance ORDER BY create_time DESC,id DESC),\n" +
                            "   \t',',1\n" +
                            "   )) closeBalance","(SUBSTRING_INDEX(\n" +
                            "   \tGROUP_CONCAT(charge_balance-charge_money ORDER BY create_time,id ASC),\n" +
                            "   \t',',1\n" +
                            "   )) openBalance",
                    "account_no",
                    "SUM(if(cons_state<10,charge_money,0)) as consumeAmount",
                    "SUM(if(cons_state<10 and charge_money<>0,1,0)) as consumeNumber",
                    "SUM(if(cons_state in(10,11),charge_money,0)) as rechargeAmount",
                    "SUM(if(cons_state in(10,11) and charge_money<>0,1,0)) as rechargeNumber",
                    "SUM(if(cons_state=21,charge_money,0)) as settleAmount",
                    "SUM(if(cons_state=21,1,0)) as settleNumber");
        }else {
            queryWrapper.select("(SUBSTRING_INDEX(\n" +
                            "   \tGROUP_CONCAT(sub_balance ORDER BY create_time DESC,id DESC),\n" +
                            "   \t',',1\n" +
                            "   )) closeBalance","(SUBSTRING_INDEX(\n" +
                            "   \tGROUP_CONCAT(sub_balance-sub_money ORDER BY create_time,id ASC),\n" +
                            "   \t',',1\n" +
                            "   )) openBalance",
                    "account_no",
                    "SUM(if(cons_state<10,sub_money,0)) as consumeAmount",
                    "SUM(if(cons_state<10 and sub_money<>0,1,0)) as consumeNumber",
                    "SUM(if(cons_state in(10,12),sub_money,0)) as rechargeAmount",
                    "SUM(if(cons_state in(12) and sub_money<>0,1,0)) as rechargeNumber",
                    "SUM(if(cons_state=21,sub_money,0)) as settleAmount",
                    "SUM(if(cons_state=21,1,0)) as settleNumber");
        }


        String start = month.replace("-", "");
        String end = month.replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","钱包余额,查询充值钱包",month,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",comId);
        queryWrapper.eq("sub_account",subAccount);
        queryWrapper.between("create_time",firstDateD,yesterdayD);
        queryWrapper.isNotNull("account_no");
        queryWrapper.groupBy("account_no");
        //queryWrapper.lt("op_type_diff",200);
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> subsidyDaily(Long comId, String endDate, boolean isLastDayOfMonth,Integer subAccount) throws Exception{
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("DATE_FORMAT(create_time,'%Y-%m-%d') AS date",
                "SUM(case when cons_state in (1,3,4,5) then - sub_money else 0 end) AS consumeAmount",
                "SUM(case when cons_state in (1,3,4,5) and sub_money<>0 then 1 else 0 end) AS consumeNumber",
                "SUM(case when cons_state in (2,6) then sub_money else 0 end) AS consCancelAmount",
                "SUM(case when cons_state in (2,6) and sub_money<>0 then 1 else 0 end) AS consCancelNumber",
                "SUM(case when cons_state in (10,12) then sub_money else 0 end) AS rechargeAmount",
                "SUM(case when cons_state in (10,12) and sub_money<>0 then 1 else 0 end) AS rechargeNumber",
                "SUM(case when cons_state in (13,21) then - sub_money else 0 end) AS settleAmount",
                "SUM(case when cons_state in (13,21) and sub_money<>0 then 1 else 0 end) AS settleNumber");
        String  mon=DateUtils.getendDate(DateUtils.getDate(endDate,"yyyy-MM-dd"),"yyyy-MM");
        String start = mon.replace("-", "");
        String end = mon.replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","钱包余额,查询充值钱包",endDate,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",comId);
        if (!isLastDayOfMonth){
            queryWrapper.apply("DATE_FORMAT(create_time,'%Y-%m-%d')<="+"'"+endDate+"'");
        }
        queryWrapper.eq("sub_account",subAccount);
        queryWrapper.groupBy("DATE_FORMAT(create_time, '%Y-%m-%d')");
        queryWrapper.orderByAsc("date");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> rechargeDaily(Long comId, String endDate, boolean isLastDayOfMonth,Integer subAccount) throws Exception{
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("DATE_FORMAT(create_time, '%Y-%m-%d') AS date",
                "SUM(case when cons_state in (1,3,4,5) then -charge_money else 0 end) AS consumeAmount",
                "SUM(case when cons_state in (1,3,4,5) and charge_money<>0 then 1 else 0 end) AS consumeNumber",
                "SUM(case when cons_state in (2,6) then charge_money else 0 end) AS consCancelAmount",
                "SUM(case when cons_state in (2,6) and charge_money<>0 then 1 else 0 end) AS consCancelNumber",
                "SUM(case when cons_state = 10 and op_type_diff>1 then charge_money else 0 end) AS rechargeAmount",
                "SUM(case when cons_state = 10 and op_type_diff>1 then 1 else 0 end) AS rechargeNumber",
                "SUM(case when cons_state = 10 and op_type_diff=1 then charge_money else 0 end) AS cashAmount",
                "SUM(case when cons_state = 10 and op_type_diff=1 then 1 else 0 end) AS cashNumber",
                "SUM(case when cons_state = 11 and op_type_diff=1 then -charge_money else 0 end) AS cashoutAmount",
                "SUM(case when cons_state = 11 and op_type_diff=1 then 1 else 0 end) AS cashoutNumber",
                "SUM(case when cons_state = 21 then -charge_money else 0 end) AS settleAmount",
                "SUM(case when cons_state = 21 then 1 else 0 end) AS settleNumber");
        String  mon=DateUtils.getendDate(DateUtils.getDate(endDate,"yyyy-MM-dd"),"yyyy-MM");
        String start = mon.replace("-", "");
        String end = mon.replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","钱包余额,查询充值钱包",endDate,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",comId);
        if (!isLastDayOfMonth){
            queryWrapper.apply("DATE_FORMAT(create_time,'%Y-%m-%d')<="+"'"+endDate+"'");
        }
        queryWrapper.eq("sub_account",subAccount);
        queryWrapper.groupBy("DATE_FORMAT(create_time, '%Y-%m-%d')");
        queryWrapper.orderByAsc("date");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> cashMonthly(Long comId, String endDate, boolean isLastDayOfMonth,Integer subAccount,String merchIds) throws Exception{
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(
                "admin_id AS mngUserId",
                "device_no",
                "device_name",
                "SUM(case when cons_state = 10 and op_type_diff=1 then charge_money else 0 end) AS cashAmount",
                "SUM(case when cons_state = 10 and op_type_diff=1 then 1 else 0 end) AS cashNumber",
                "SUM(case when cons_state = 11 and op_type_diff=1 then charge_money else 0 end) AS cashoutAmount",
                "SUM(case when cons_state = 11 and op_type_diff=1 then 1 else 0 end) AS cashoutNumber");
        String  mon=DateUtils.getendDate(DateUtils.getDate(endDate,"yyyy-MM-dd"),"yyyy-MM");
        String start = mon.replace("-", "");
        String end = mon.replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","钱包余额,查询充值钱包",endDate,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",comId);
        if (!isLastDayOfMonth){
            queryWrapper.apply("DATE_FORMAT(create_time,'%Y-%m-%d')<="+"'"+endDate+"'");
        }
        queryWrapper.eq("sub_account",subAccount);
        if (!CommonUtil.isNull(merchIds)) {
            String[] idArr = merchIds.split(",");
            List<Integer> idList = Stream.of(idArr).map(Integer::parseInt).collect(Collectors.toList());
            queryWrapper.in("merch_id", idList);
        }

        queryWrapper.groupBy("admin_id","admin_name","device_no","device_name");
        queryWrapper.orderByAsc("admin_id","device_no");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> cashDaily(CashDailyRequest cashDailyRequest, boolean isLastDayOfMonth) throws Exception{
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(
                "DATE_FORMAT(create_time, '%Y-%m-%d') AS date",
                "SUM(case when cons_state = 10 and op_type_diff=1 then charge_money else 0 end) AS cashAmount",
                "SUM(case when cons_state = 10 and op_type_diff=1 then 1 else 0 end) AS cashNumber",
                "SUM(case when cons_state = 11 and op_type_diff=1 then charge_money else 0 end) AS cashoutAmount",
                "SUM(case when cons_state = 11 and op_type_diff=1 then 1 else 0 end) AS cashoutNumber");
        String  mon=DateUtils.getendDate(DateUtils.getDate(cashDailyRequest.getEndDate(),"yyyy-MM-dd"),"yyyy-MM");
        String start = mon.replace("-", "");
        String end = mon.replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","现金日报",cashDailyRequest.getEndDate(),e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",cashDailyRequest.getComId());
        if (!isLastDayOfMonth){
            queryWrapper.apply("DATE_FORMAT(create_time,'%Y-%m-%d')<="+"'"+cashDailyRequest.getEndDate()+"'");
        }
        if (cashDailyRequest.getType()==1){
            queryWrapper.eq("admin_id",cashDailyRequest.getMngUserId());
        }else {
            queryWrapper.eq("device_no",cashDailyRequest.getDeviceNo());
        }
        queryWrapper.eq("sub_account",cashDailyRequest.getSubAccount());
        queryWrapper.groupBy("DATE_FORMAT(create_time, '%Y-%m-%d')");
        queryWrapper.orderByAsc("date");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> merchMonthly(Long comId, String endDate, boolean isLastDayOfMonth,Integer subAccount,String merchIds) throws Exception {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(
                "merch_id",
                "merch_name",
                "count(id) AS consNumber",
                "SUM(charge_money) AS chargeAmount",
                "SUM(sub_money) AS subAmount",
                "SUM(real_money) AS consAmount");
        String  mon=DateUtils.getendDate(DateUtils.getDate(endDate,"yyyy-MM-dd"),"yyyy-MM");
        String start = mon.replace("-", "");
        String end = mon.replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","商户消费月报",endDate,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",comId);
        if (!isLastDayOfMonth){
            queryWrapper.apply("DATE_FORMAT(create_time,'%Y-%m-%d')<="+"'"+endDate+"'");
        }
        queryWrapper.eq("op_type_diff",100);
        queryWrapper.eq("sub_account",subAccount);
        queryWrapper.ne("real_money",0);
        if (!CommonUtil.isNull(merchIds)) {
            String[] idArr = merchIds.split(",");
            List<Integer> idList = Stream.of(idArr).map(Integer::parseInt).collect(Collectors.toList());
            queryWrapper.in("merch_id", idList);
        }
        queryWrapper.groupBy("merch_id","merch_name");
        queryWrapper.orderByAsc("merch_id");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> merchDaily(Long comId, String endDate, boolean isLastDayOfMonth, Integer merchId,Integer subAccount) throws Exception {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(
                "DATE_FORMAT(create_time, '%Y-%m-%d') AS date",
                "SUM(case when cons_state in (1,3,4,5) then -real_money else 0 end) AS consumeAmount",
                "SUM(case when cons_state in (1,3,4,5) then 1 else 0 end) AS consumeNumber",
                "SUM(case when cons_state in (2,6) then real_money else 0 end) AS consCancelAmount",
                "SUM(case when cons_state in (2,6) then 1 else 0 end) AS consCancelNumber",
                "SUM(charge_money) AS chargeAmount",
                "SUM(sub_money) AS subAmount");
        String  mon=DateUtils.getendDate(DateUtils.getDate(endDate,"yyyy-MM-dd"),"yyyy-MM");
        String start = mon.replace("-", "");
        String end = mon.replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","商户消费月报",endDate,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",comId);
        if (!isLastDayOfMonth){
            queryWrapper.apply("DATE_FORMAT(create_time,'%Y-%m-%d')<="+"'"+endDate+"'");
        }
        if (merchId !=null){
            queryWrapper.eq("merch_id",merchId);
        }
        queryWrapper.eq("op_type_diff",100);
        queryWrapper.eq("sub_account",subAccount);
        queryWrapper.ne("real_money",0);
        queryWrapper.groupBy("DATE_FORMAT(create_time, '%Y-%m-%d')");
        queryWrapper.orderByAsc("date");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> dayTotalPayType(JSONObject jsonObject) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("DATE_FORMAT(create_time, '%Y-%m-%d') stime",
                "op_type",
                "sum(case when  cons_state in (1,3,5)  then 1 else 0 end) orderNum",
                "sum(case when  cons_state in (1,3,5)  then real_money else 0 end) amount",
                "sum(case when  cons_state in (2,6) then  1 else 0 end) refundNum",
                "sum(case when  cons_state in (2,6) then  real_money else 0 end) refundAmount");
        String start = jsonObject.getString("summaryDate").replace("-", "");
        String end = jsonObject.getString("summaryDate").replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每天各餐段汇总结果",jsonObject,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });

        queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
        if (jsonObject.getLong("merchID")!=-1){
            queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
        }
        queryWrapper.in("cons_state",1,2,3,5,6);

        queryWrapper.groupBy("DATE_FORMAT(create_time, '%Y-%m-%d'),op_type");
        queryWrapper.orderByAsc("DATE_FORMAT(create_time, '%Y-%m-%d'),op_type");
        return xfRecordMapper.selectList(queryWrapper);
    }

  @Override
  public List<EntityXfRecord> dayTotalPayTypeXM(JSONObject jsonObject) {
    QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("DATE_FORMAT(create_time, '%Y-%m-%d') stime",
        "op_type",
        "sum(case when  cons_state in (1,3,5)  then 1 else 0 end) orderNum",
        "sum(case when  cons_state in (1,3,5)  then real_money else 0 end) amount",
        "sum(case when  cons_state in (2,6) then  1 else 0 end) refundNum",
        "sum(case when  cons_state in (2,6) then  real_money else 0 end) refundAmount");
    String start = jsonObject.getString("summaryDate").replace("-", "");
    String end = jsonObject.getString("summaryDate").replace("-", "");
    List<String> dates = new ArrayList<>();
    try {
      dates = DateUtils.getMonthBetween(start, end);
    } catch (Exception e) {
      log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每天各餐段汇总结果",jsonObject,e);
    }

    List<String> finalDates = dates;
    //日期范围查询
    queryWrapper.and(wrapper -> {
      for (String info : finalDates) {
        wrapper.or().eq("month_value", info);
      }
    });

    queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
    if (jsonObject.getLong("merchID")!=-1){
      queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
    }
    queryWrapper.in("cons_state",1,2,3,5,6);

    queryWrapper.groupBy("DATE_FORMAT(create_time, '%Y-%m-%d'),op_type");
    queryWrapper.orderByAsc("DATE_FORMAT(create_time, '%Y-%m-%d'),op_type");
    return xfRecordMapper.selectList(queryWrapper);
  }

    @Override
    public List<EntityXfRecord> dayTotalAccount(JSONObject jsonObject) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("DATE_FORMAT(pay_time, '%Y-%m-%d') stime",
                "op_type_diff as reportType",
                "sum(case when cons_state in (1,3,5) then  1 else 0 end) orderNum",
                "sum(case when cons_state in (1,3,5) then  real_money else 0 end) amount",
                "sum(case when cons_state in (2,6) then  1 else 0 end) refundNum",
                "sum(case when cons_state in (2,6) then  real_money else 0 end) refundAmount",
                "sum(case when (cons_state =10 and op_type=12) then  1 else 0 end) chargeNum",
                "sum(case when (cons_state =10 and op_type=12) then  cons_money else 0 end) chargeAmount");

        String start = jsonObject.getString("summaryDate").replace("-", "");
        String end = jsonObject.getString("summaryDate").replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每天各餐段汇总结果",jsonObject,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });

        queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
        if (jsonObject.getLong("merchID")!=-1){
            queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
        }
        queryWrapper.in("cons_state",1,2,3,5,6,10);
        //queryWrapper.ge("op_type_diff",100);
        queryWrapper.and(wrapper -> wrapper.ge("op_type_diff",100).or().eq("op_type_diff",0));

        queryWrapper.groupBy("DATE_FORMAT(pay_time, '%Y-%m-%d')","op_type_diff");
        queryWrapper.orderByAsc("DATE_FORMAT(pay_time, '%Y-%m-%d')");
        return xfRecordMapper.selectList(queryWrapper);
    }

  @Override
  public List<EntityXfRecord> dayTotalAccountXM(JSONObject jsonObject) {
    QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("DATE_FORMAT(create_time, '%Y-%m-%d') stime",
        "op_type_diff as reportType",
        "sum(case when cons_state in (1,3,5) then  1 else 0 end) orderNum",
        "sum(case when cons_state in (1,3,5) then  real_money else 0 end) amount",
        "sum(case when cons_state in (2,6) then  1 else 0 end) refundNum",
        "sum(case when cons_state in (2,6) then  real_money else 0 end) refundAmount",
        "sum(case when (cons_state =10 and op_type=12) then  1 else 0 end) chargeNum",
        "sum(case when (cons_state =10 and op_type=12) then  cons_money else 0 end) chargeAmount");

    String start = jsonObject.getString("summaryDate").replace("-", "");
    String end = jsonObject.getString("summaryDate").replace("-", "");
    List<String> dates = new ArrayList<>();
    try {
      dates = DateUtils.getMonthBetween(start, end);
    } catch (Exception e) {
      log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每天各餐段汇总结果",jsonObject,e);
    }

    List<String> finalDates = dates;
    //日期范围查询
    queryWrapper.and(wrapper -> {
      for (String info : finalDates) {
        wrapper.or().eq("month_value", info);
      }
    });

    queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
    if (jsonObject.getLong("merchID")!=-1){
      queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
    }
    queryWrapper.in("cons_state",1,2,3,5,6,10);
    //queryWrapper.ge("op_type_diff",100);
    queryWrapper.and(wrapper -> wrapper.ge("op_type_diff",100).or().eq("op_type_diff",0));

    queryWrapper.groupBy("DATE_FORMAT(create_time, '%Y-%m-%d')","op_type_diff");
    queryWrapper.orderByAsc("DATE_FORMAT(create_time, '%Y-%m-%d')");
    return xfRecordMapper.selectList(queryWrapper);
  }

    @Override
    public List<EntityXfRecord> monthTotalMealNo(JSONObject jsonObject, String start, String end) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("DATE_FORMAT(cons_time, '%Y-%m') stime",
                "case when cons_type=1  then  (case when meal_no='' or meal_no is null  then '0' else meal_no end ) else '-1' end  reportType",
                "sum(case when  cons_state in (1,5)  then 1 else 0 end) orderNum",
                "sum(case when  cons_state in (1,5)  then real_money else 0 end) amount",
                "sum(case when  cons_state in (2,6) then  1 else 0 end) refundNum",
                "sum(case when  cons_state in (2,6) then  real_money else 0 end) refundAmount");

        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每月各餐段汇总结果",jsonObject,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
        if (jsonObject.getLong("merchID")!=-1){
            queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
        }
        queryWrapper.in("cons_state",1,2,5,6);
        queryWrapper.groupBy("DATE_FORMAT(cons_time, '%Y-%m')","cons_type","meal_no");
        queryWrapper.orderByAsc("DATE_FORMAT(cons_time, '%Y-%m')");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> monthTotalPayType(JSONObject jsonObject, String start, String end) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("DATE_FORMAT(create_time, '%Y-%m') stime",
                "op_type as reportType",
                "sum(case when  cons_state in (1,5)  then 1 else 0 end) orderNum",
                "sum(case when  cons_state in (1,5)  then real_money else 0 end) amount",
                "sum(case when  cons_state in (2,6) then  1 else 0 end) refundNum",
                "sum(case when  cons_state in (2,6) then  real_money else 0 end) refundAmount");

        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每月各支付方式汇总结果",jsonObject,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
        if (jsonObject.getLong("merchID")!=-1){
            queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
        }
        queryWrapper.in("cons_state",1,2,5,6);
        queryWrapper.groupBy("DATE_FORMAT(create_time, '%Y-%m')","op_type");
        queryWrapper.orderByAsc("DATE_FORMAT(create_time, '%Y-%m')","op_type");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> monthTotalAccount(JSONObject jsonObject, String start, String end) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("DATE_FORMAT(pay_time, '%Y-%m') stime",
                "op_type_diff as reportType",
                "sum(case when cons_state in (1,5) then  1 else 0 end) orderNum",
                "sum(case when cons_state in (1,5) then  real_money else 0 end) amount",
                "sum(case when cons_state in (2,6) then  1 else 0 end) refundNum",
                "sum(case when cons_state in (2,6) then  real_money else 0 end) refundAmount");

        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","查询每月各收款账户汇总结果",jsonObject,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",jsonObject.getLong("companyID"));
        if (jsonObject.getLong("merchID")!=-1){
            queryWrapper.eq("merch_id",jsonObject.getLong("merchID"));
        }
        queryWrapper.in("cons_state",1,2,5,6);
        queryWrapper.groupBy("DATE_FORMAT(pay_time, '%Y-%m')","op_type_diff");
        queryWrapper.orderByAsc("DATE_FORMAT(pay_time, '%Y-%m')","op_type_diff");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public Page<EntityXfRecord> getConsPerReports(GetConsPerReportsRequest getConsPerReportsRequest) throws Exception{
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        String sTime="'"+getConsPerReportsRequest.getStartTime()+"~"+getConsPerReportsRequest.getEndTime()+"'";
        queryWrapper.select(
                sTime + "as sTime",
                "dept_id",
                "dept_name",
                "p_code",
                "p_name",
                "card_no",
                "sum(cons_money) as allAmount",
                "sum(discount) as allDiscount",
                "sum(real_money) as allRealAmount",
                "sum(1) as allTimes",
                "sum(case when op_type_diff >200 then real_money else 0 end) as dkAmount",
                "sum(case when op_type_diff >200  then 1 else 0 end) as dkTimes",
                "sum(case when op_type_diff=100 then charge_money else 0 end) as xjAmount",
                "sum(case when op_type_diff=100 then sub_money else 0 end) as btAmount",
                "sum(case when op_type_diff=100 then par_value else 0 end) as yhqAmount",
                "sum(case when op_type_diff=100 then 1 else 0 end) as xnzhTimes",
                "sum(case when op_type_diff=0 then real_money else 0 end) as jcAmount",
                "sum(case when op_type_diff=0 then 1 else 0 end) as jcTimes"
        );
        String start = DateUtils.getendDate(DateUtils.getDate(getConsPerReportsRequest.getStartTime(),"yyyy-MM-dd"),"yyyy-MM").replace("-", "");
        String end = DateUtils.getendDate(DateUtils.getDate(getConsPerReportsRequest.getEndTime(),"yyyy-MM-dd"),"yyyy-MM").replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取人员消费汇总报表",end,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",getConsPerReportsRequest.getComId());
        queryWrapper.eq("pay_state",1);
        queryWrapper.in("op_type",1,21,22,23,9,12,25);
        queryWrapper.in("cons_state",1,2,3,5,6);
        String startTime=getConsPerReportsRequest.getStartTime()+" 00:00:00";
        String endTime=getConsPerReportsRequest.getEndTime()+" 23:59:59";
        queryWrapper.between("create_time",startTime,endTime);
        if (!getConsPerReportsRequest.getDeptIds().equals("-1")){
            String[] arr=getConsPerReportsRequest.getDeptIds().split(",");
            queryWrapper.in("dept_id",arr);
        }
        if (StringUtils.isNotEmpty(getConsPerReportsRequest.getPCode())){
            queryWrapper.like("p_code",getConsPerReportsRequest.getPCode());
        }
        if (getConsPerReportsRequest.getPId()!=null){
            queryWrapper.eq("p_id",getConsPerReportsRequest.getPId());
        }
        if (StringUtils.isNotEmpty(getConsPerReportsRequest.getPName())){
            queryWrapper.like("p_name",getConsPerReportsRequest.getPName());
        }
        if (StringUtils.isNotEmpty(getConsPerReportsRequest.getCardNo())){
            queryWrapper.like("card_no",getConsPerReportsRequest.getCardNo());
        }
        Page<EntityXfRecord> page = new Page<>(getConsPerReportsRequest.getPage(), getConsPerReportsRequest.getSize());
        queryWrapper.groupBy("dept_id","p_id");
        queryWrapper.orderByAsc("dept_id","p_code","p_name","card_no");
        return xfRecordMapper.selectPage(page,queryWrapper);
    }

    @Override
    public List<EntityXfRecord> getConsDeptReports(GetConsDeptReportsRequest getConsDeptReportsRequest) throws Exception{
        if (getConsDeptReportsRequest.getSize()!=-1 && getConsDeptReportsRequest.getPage() !=-1){
            PageHelper.startPage(getConsDeptReportsRequest.getPage(),getConsDeptReportsRequest.getSize());
        }
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        String sTime="'"+getConsDeptReportsRequest.getStartTime()+"~"+getConsDeptReportsRequest.getEndTime()+"'";
        queryWrapper.select(
                sTime + "as sTime",
                "dept_id",
                "meal_no",
                "dept_name",
                "meal_no",
                "sum(cons_money) as allAmount",
                "sum(discount) as allDiscount",
                "sum(real_money) as allRealAmount",
                "sum(1) as allTimes",
                "sum(case when op_type_diff >200 then real_money else 0 end) as dkAmount",
                "sum(case when op_type_diff >200  then 1 else 0 end) as dkTimes",
                "sum(case when op_type_diff=100 then charge_money else 0 end) as xjAmount",
                "sum(case when op_type_diff=100 then sub_money else 0 end) as btAmount",
                "sum(case when op_type_diff=100 then par_value else 0 end) as yhqAmount",
                "sum(case when op_type_diff=100 then 1 else 0 end) as xnzhTimes",
                "sum(case when op_type_diff=0 then real_money else 0 end) as jcAmount",
                "sum(case when op_type_diff=0 then 1 else 0 end) as jcTimes"
        );
        String start = DateUtils.getendDate(DateUtils.getDate(getConsDeptReportsRequest.getStartTime(),"yyyy-MM-dd"),"yyyy-MM").replace("-", "");
        String end = DateUtils.getendDate(DateUtils.getDate(getConsDeptReportsRequest.getEndTime(),"yyyy-MM-dd"),"yyyy-MM").replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取部门消费汇总报表",end,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",getConsDeptReportsRequest.getComId());
        queryWrapper.eq("pay_state",1);
//        queryWrapper.in("op_type",1,21,22,23);
        queryWrapper.lt("cons_state",10);
        String startTime=getConsDeptReportsRequest.getStartTime()+" 00:00:00";
        String endTime=getConsDeptReportsRequest.getEndTime()+" 23:59:59";
        queryWrapper.between("create_time",startTime,endTime);
        if (!getConsDeptReportsRequest.getDeptIds().equals("-1")){
            String[] arr=getConsDeptReportsRequest.getDeptIds().split(",");
            queryWrapper.in("dept_id",arr);
        }
        queryWrapper.groupBy("dept_id" ,"meal_no");
        queryWrapper.orderByAsc("dept_id");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> getConsMerchReports(Long comId, String startTime, String endTime, String merchIds) throws Exception{
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(
                "merch_id",
                "sum(real_money) as merchAmount",
                "sum(1) as merchNumber",
                "sum(cons_money) as merchConsAmount",
                "sum(discount) as merchDiscount",
                "sum(case when op_type_diff =100 then real_money else 0 end) as cardAmount",
                "sum(case when op_type_diff =100 then 1 else 0 end) as cardNumber",
                "sum(case when op_type_diff =0 then real_money else 0 end) as timesAmount",
                "sum(case when op_type_diff =0 then 1 else 0 end) as timesNumber",
                "count(DISTINCT (meal_date)) as amount1"
        );
        String start = DateUtils.getendDate(DateUtils.getDate(startTime,"yyyy-MM-dd"),"yyyy-MM").replace("-", "");
        String end = DateUtils.getendDate(DateUtils.getDate(endTime,"yyyy-MM-dd"),"yyyy-MM").replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取商户消费汇总报表",end,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",comId);
        queryWrapper.eq("pay_state",1);
        queryWrapper.in("cons_state",1,2,3,5,6);
        startTime=startTime+" 00:00:00";
        endTime=endTime+" 23:59:59";
        queryWrapper.between("create_time",startTime,endTime);
        if (!merchIds.equals("")){
            String[] arr=merchIds.split(",");
            queryWrapper.in("merch_id",arr);
        }
        queryWrapper.groupBy("merch_id");
        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public List<EntityXfRecord> getAccDiscountReports(AccDiscountReportsOnShowRequest request)throws Exception{
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();

        switch (request.getType()) {
            case MEAL_DATA :
                queryWrapper.select(
                        "acc_type_id",
                        "Date(create_time) as meal_date",
                        "meal_no",
//                        "count(cons_money) as consTimes",
                        "sum(case when  cons_state in  (2,6) then -1  else  1 end ) consTimes",
                        "sum(0-cons_money) as consAmount",
                        "sum(0-real_money) as realMoney",
                        "sum(receipt_price) as receiptPrice"
                );
                queryWrapper.groupBy("acc_type_id", "meal_no");
                queryWrapper.orderByDesc("acc_type_id", "meal_no");
                break;
            case DIS_DATA :
                queryWrapper.select(
                        "acc_type_id",
                        "Date(create_time) as meal_date",
                        "discount",
//                        "count(discount) as disTimes",
                        "sum(case when  cons_state in  (2,6) then -1  else  1 end )  disTimes",
                        "sum(discount) as disSums"
                );
                queryWrapper.apply("ABS(discount) > 0");

                queryWrapper.groupBy("acc_type_id", "ABS(discount)");
                queryWrapper.orderByDesc("acc_type_id", "ABS(discount)");
                break;
            case SUM_DATA :
                queryWrapper.select(
                        "acc_type_id",
                        "Date(create_time) as meal_date",
                        "sum(0-cons_money) as consSums",
                        "sum(discount) as disSums",
                        "sum(0-real_money) as realSums",
                        "sum(receipt_price) as receiptPrice",
                        "sum(case when  cons_state in  (2,6) then -1  else  1 end ) consTimes"
                );
                queryWrapper.groupBy("acc_type_id");
                queryWrapper.orderByDesc("acc_type_id");
                break;
            default:
                return null;
        }


        if(request.getReportType() == 1){
            queryWrapper.groupBy(" Date(create_time) ");
        }

        String startTime = request.getStartTime();
        String endTime = request.getEndTime();
        String start = DateUtils.getendDate(DateUtils.getDate(startTime,"yyyy-MM-dd"),"yyyy-MM").replace("-", "");
        String end = DateUtils.getendDate(DateUtils.getDate(endTime,"yyyy-MM-dd"),"yyyy-MM").replace("-", "");

        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","按账户类型、优惠金额汇总",end,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id", request.getComId());
        queryWrapper.eq("pay_state",1);
        queryWrapper.in("cons_state",1,2,3,5,6);
//        queryWrapper.eq("if_refund", 0);
        if(request.getMerchId()!=null){
            queryWrapper.eq("merch_id",request.getMerchId());
        }

        startTime = startTime+" 00:00:00";
        endTime = endTime+" 23:59:59";
        queryWrapper.between("create_time",startTime,endTime);
        if (null != request.getPointId()){
            queryWrapper.eq("point_id", request.getPointId());
        }

        if (org.apache.commons.lang3.StringUtils.isNotBlank(request.getDeptIds())){
            String[] arr = request.getDeptIds().split(",");
            queryWrapper.in("dept_id",arr);
        }

        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public EntityXfRecord getPointIdByNameFromRecord(JSONObject jsonObject) throws Exception{
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        String start = DateUtils.getendDate(DateUtils.getDate(jsonObject.getString("summaryDate"),"yyyy-MM-dd"),"yyyy-MM").replace("-", "");
        String end = DateUtils.getendDate(DateUtils.getDate(jsonObject.getString("summaryDate"),"yyyy-MM-dd"),"yyyy-MM").replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取商户消费汇总报表",end,e);
        }

        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.eq("com_id",jsonObject.getLong("companyId"));
        queryWrapper.eq("point_name",jsonObject.getString("pointName"));
        queryWrapper.ge("cons_time",jsonObject.getString("summaryDate"));
        queryWrapper.orderByAsc("cons_time");
        queryWrapper.last(" limit 1");
        return xfRecordMapper.selectOne(queryWrapper);
    }

    @Override
    public EntityXfRecord getEntityXfRecord(String orderNo, String monthValue) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("order_no", orderNo);
        queryWrapper.eq("month_value", monthValue);
        queryWrapper.last(" limit 1");
        return xfRecordMapper.selectOne(queryWrapper);
    }

    @Override
    public List<ConRecCountVo> getXfCountByComId(Long beginComId, Long endComId, String month) {
        QueryWrapper<EntityXfRecord> query=new QueryWrapper<>();
        query.select("com_id","count(id) as amount3");
        query.ge("com_id",beginComId);
        query.le("com_id",endComId);
        query.eq("month_value",month);
        query.groupBy("com_id");
        List<EntityXfRecord> list =  xfRecordMapper.selectList(query);
        if(list==null|| list.size() == 0){
            return new ArrayList<>();
        }
        List<ConRecCountVo> conRecCountVos =new ArrayList<>();

        for (EntityXfRecord entityXfRecord:list){
            ConRecCountVo conRecCountVo = new ConRecCountVo();
            conRecCountVo.setComId(entityXfRecord.getComId());
            conRecCountVo.setXfRecNum(entityXfRecord.getAmount3());
            conRecCountVos.add(conRecCountVo);
        }
        return conRecCountVos;
    }

    @Override
    public Integer getXfCount(Long beginComId, Long endComId, String month) {
        QueryWrapper<EntityXfRecord> query = Wrappers.query();
        query.ge("com_id",beginComId);
        query.le("com_id",endComId);
        query.eq("month_value",month);
        return  xfRecordMapper.selectCount(query);
    }

    @Override
    public Result getXfCount(Long comId, String beginMonth, String endMonth) {
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(beginMonth, endMonth);
        } catch (Exception e) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }
//        if (dates.size() > 6) {
//            return ResultUtil.error(ResultEnum.Err_2034);
//        }
        QueryWrapper<EntityXfRecord> queryWrapper = Wrappers.query();
        if(comId>0l){
            queryWrapper.eq("com_id",comId);
        }
        queryWrapper.le("cons_state",10);
        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });


        queryWrapper.select("month_value","count(id) as amount3","SUM(ABS(cons_money)) as amount","count(DISTINCT (device_no)) as amount1","count( DISTINCT (p_id)) as amount2");
        //,"count(DISTINCT (device_no)) as amount1","count( DISTINCT (p_id)) as amount2"
        queryWrapper.groupBy("month_value");
        List<EntityXfRecord> countRecNum = xfRecordMapper.selectList(queryWrapper);

        if(countRecNum==null|| countRecNum.size() == 0){
            return ResultUtil.success(new ArrayList<>());
        }
        List<ConRecCountVo> conRecCountVos =new ArrayList<>();

        for (EntityXfRecord entityXfRecord:countRecNum){
            ConRecCountVo conRecCountVo = new ConRecCountVo();

            QueryWrapper<EntityXfRecord> queryWrapper1 =  Wrappers.query();
            if(comId>0l){
                queryWrapper1.eq("com_id",comId);
            }
            queryWrapper1.eq("month_value", entityXfRecord.getMonthValue());
            queryWrapper1.apply("device_no is not null");
            queryWrapper1.select("count(DISTINCT (device_no)) as amount1");
            queryWrapper1.groupBy("month_value");
            EntityXfRecord countRecNum2 = xfRecordMapper.selectOne(queryWrapper1);

            if(countRecNum2==null || countRecNum2.getAmount1() == null){
                conRecCountVo.setXfMachNum(0);
            }else{
                conRecCountVo.setXfMachNum(countRecNum2.getAmount1());
            }


            QueryWrapper<EntityXfRecord> queryWrapper2 =  Wrappers.query();
            if(comId>0l){
                queryWrapper2.eq("com_id",comId);
            }
            queryWrapper2.eq("month_value", entityXfRecord.getMonthValue());
            queryWrapper2.apply("p_id is not null");
            queryWrapper2.select("count(DISTINCT (p_id)) as amount1");
            queryWrapper2.groupBy("month_value");
            EntityXfRecord countRecNum3 = xfRecordMapper.selectOne(queryWrapper2);

            if(countRecNum3==null || countRecNum3.getAmount1()==null){
                conRecCountVo.setUserNum(0);
            }else{
                conRecCountVo.setUserNum(countRecNum3.getAmount1());
            }

            conRecCountVo.setAmount(entityXfRecord.getAmount());
            conRecCountVo.setMonthValue(entityXfRecord.getMonthValue());
            conRecCountVo.setXfRecNum(entityXfRecord.getAmount3());
            conRecCountVos.add(conRecCountVo);
        }
        return ResultUtil.success(conRecCountVos);
    }

    @Override
    public Result getXfCount(Long beginComId, Long endComId, String beginMonth, String endMonth) {
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(beginMonth, endMonth);
        } catch (Exception e) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }
//        if (dates.size() > 6) {
//            return ResultUtil.error(ResultEnum.Err_2034);
//        }
        QueryWrapper<EntityXfRecord> queryWrapper = Wrappers.query();
//        queryWrapper.between("com_id",beginComId,endComId);
        queryWrapper.ge("com_id",beginComId);
        queryWrapper.le("com_id",endComId);
        queryWrapper.le("cons_state",10);
        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });

        queryWrapper.select("month_value","count(id) as amount3","count( DISTINCT device_no) as amount1","count( DISTINCT p_id) as amount2","SUM(ABS(cons_money)) as amount");
        queryWrapper.groupBy("month_value");
        List<EntityXfRecord> countRecNum = xfRecordMapper.selectList(queryWrapper);

        if(countRecNum==null|| countRecNum.size() == 0){
            return ResultUtil.success(new ArrayList<>());
        }
        List<ConRecCountVo> conRecCountVos =new ArrayList<>();

        for (EntityXfRecord entityXfRecord:countRecNum){
            ConRecCountVo conRecCountVo = new ConRecCountVo();
            conRecCountVo.setXfMachNum(entityXfRecord.getAmount1());
            conRecCountVo.setUserNum(entityXfRecord.getAmount2());
            conRecCountVo.setAmount(entityXfRecord.getAmount());
            conRecCountVo.setXfRecNum(entityXfRecord.getAmount3());
            conRecCountVo.setMonthValue(entityXfRecord.getMonthValue());
            conRecCountVos.add(conRecCountVo);
        }
        return ResultUtil.success(conRecCountVos);
    }

    @Override
    public Result getChannelData(Long comId,String payChannelNo, String startTime, String endTime) {
        QueryWrapper<EntityXfRecord> queryWrapper = Wrappers.query();
        queryWrapper.eq("com_id",comId);
        String start = startTime.substring(0, 7).replace("-", "");
        String end =   endTime.substring(0, 7).replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",1,e);
        }
        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });

        //小于等于
        queryWrapper.ge("create_time", DateUtils.strToDate(startTime+" 00:00:00"));
        //大于等于
        queryWrapper.le("create_time", DateUtils.strToDate(endTime+" 23:59:59"));

        //and	cons_state in (1,2,3,5,6) 	and	pay_state = 1
        queryWrapper.in("cons_state",1,2,3,5,6);
        queryWrapper.eq("pay_state",1);
        queryWrapper.eq("pay_channel_no",payChannelNo);

        queryWrapper.select("pay_way","sum(real_money) as amount","sum(1) as amount1");
        queryWrapper.groupBy("pay_way");
        List<EntityXfRecord> list = xfRecordMapper.selectList(queryWrapper);

        List<ChannelDataVo>  channelDataVos = new ArrayList<>();
        if(list!=null && list.size()>0){
            for (EntityXfRecord entityXfRecord:list){
                ChannelDataVo channelDataVo =new ChannelDataVo();
                channelDataVo.setChannelNumber(entityXfRecord.getAmount1());
                channelDataVo.setChannelAmount(Math.abs(entityXfRecord.getAmount()));
                channelDataVo.setChannelPayway(entityXfRecord.getPayWay());
                channelDataVos.add(channelDataVo);
            }
        }
        return ResultUtil.success(channelDataVos);
    }

    @Override
    public Result getGroupData(GetGroupDataRequest request) {
        QueryWrapper<EntityXfRecord> queryWrapper = Wrappers.query();
        queryWrapper.eq("com_id",request.getComId());
        String start = request.getStartTime().substring(0, 7).replace("-", "");
        String end =   request.getEndTime().substring(0, 7).replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】","获取消费记录信息",1,e);
        }
        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });

        //小于等于
        queryWrapper.ge("cons_time", DateUtils.strToDate(request.getStartTime()+" 00:00:00"));
        //大于等于
        queryWrapper.le("cons_time", DateUtils.strToDate(request.getEndTime()+" 23:59:59"));

        //and	cons_state in (1,2,3,5,6) 	and	pay_state = 1
        queryWrapper.in("cons_state",1,2,3,5,6);
        queryWrapper.eq("pay_state",1);
        queryWrapper.eq("pay_channel_no",request.getPayChannelNo());
        queryWrapper.in("pay_way",request.getPayWay());

        queryWrapper.select("merch_id","pay_way","sum(real_money) as amount","sum(1) as amount1");
        queryWrapper.groupBy("merch_id","pay_way");
        List<EntityXfRecord> list = xfRecordMapper.selectList(queryWrapper);

        List<GroupDataVo>  groupDataVos = new ArrayList<>();
        if(list!=null && list.size()>0){
            for (EntityXfRecord entityXfRecord:list){
                GroupDataVo groupDataVo =new GroupDataVo();
                groupDataVo.setMerchId(entityXfRecord.getMerchId());
                groupDataVo.setChannelPayway(entityXfRecord.getPayWay());
                groupDataVo.setAmount(Math.abs(entityXfRecord.getAmount()));
                groupDataVo.setNumber(entityXfRecord.getAmount1());
                groupDataVos.add(groupDataVo);
            }
        }
        return ResultUtil.success(groupDataVos);
    }

    @Override
    public Boolean updateOrderVerification(XfOrderVerificationRequest request) {
        UpdateWrapper<EntityXfRecord> update=Wrappers.update();
        update.eq("com_id",request.getCompanyId());
        update.eq("order_no",request.getOrderNo());
        update.eq("month_value",request.getMonthValue());
        update.set("verification",1);
        return this.update(update);
    }

    @Override
    public Result getXfPayDate(Long comId, String month)  {
        Date date = null;
        try {
            date = new SimpleDateFormat("yyyy-MM-dd").parse(month);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        SimpleDateFormat targetFormat = new SimpleDateFormat("yyyyMM");
        String formatted = targetFormat.format(date);

        QueryWrapper<EntityXfRecord> queryWrapper = Wrappers.query();
        queryWrapper.select("DATE_FORMAT(create_time, '%Y-%m-%d') payDate",
                        "merch_id", "merch_name", "sum(0-real_money) payAmount")
                .eq("com_id", comId)
                .eq("month_value", formatted)
                .gt("merch_id", 0)
                .in("cons_state", 1, 2, 5, 6)
                .eq(StringUtils.isNotEmpty(month), "DATE_FORMAT(create_time, '%Y-%m-%d')", month)
                .groupBy("merch_id")
                .orderByAsc("merch_id");
        List<EntityXfRecord> entityXfRecords = xfRecordMapper.selectList(queryWrapper);
        HashMap<String, Object> map = new HashMap<>();
        map.put("list",entityXfRecords);
        return ResultUtil.success(map);

    }


    @Override
    public List<GetPointMealPreVo> getPointMealPre(GetPointMealPreReportsRequest request) {

        List<GetPointMealPreVo> pointMealPreList = new ArrayList<>();

        /**
         * -分库消费记录表xf_record_yyyyMM，单表查询示例：
         * select point_id,meal_date,meal_no
         * 	,sum(case when cons_state in (3,5) then 1  else 0 end ) as  dcTimes
         * 	,sum(case when cons_state=4 then 1  else 0 end ) as  qcTimes
         * 	,sum(case when cons_state=6 then 1  else 0 end ) as  tcTimes
         *
         * 				from xf_record_202406
         * 				where com_id =#{comId}   and cons_state in (3,4,5,6)
         *  if(#{pointId} 不为空)  and point_id = #{pointId}
         *  			and meal_date between #{startTime}  and  #{endTime}
         * group by  point_id,meal_date,meal_no
         * order  by  point_id,meal_date,meal_no
         */


        QueryWrapper<EntityXfRecord> queryWrapper = Wrappers.query();


        String lastMonth = request.getStartTime().substring(0,7).replace("-","");
        if(request.getStartTime().substring(0,7).equals(request.getEndTime().substring(0,7))){
            //同月往前查一个月
            lastMonth = DateUtils.getLastMonth(lastMonth, 1);
        }
        List<String> dates = new ArrayList<>();
        try {
            String start3 = lastMonth;
            String end3 = request.getEndTime().substring(0, 7).replace("-", "");
            dates = DateUtils.getMonthBetween(start3, end3);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】", "获取消费记录信息", request, e);
        }

        queryWrapper.select("point_id", "meal_date", "meal_no",
                        "sum(case when cons_state in (3,5) and if_refund = 0 then 1  else 0 end ) as  amount1",//dcTimes
                        "sum(case when cons_state=4 then 1  else 0 end ) as  amount3", //qcTimes
                        "sum(case when cons_state=6 then 1  else 0 end ) as  amount2")  //tcTimes
                .eq("com_id", request.getComId())
                .in("cons_state", 3, 4, 5, 6);

        if (request.getPointId() != null) {
            queryWrapper.eq("point_id", request.getPointId());
        }

        queryWrapper.between("meal_date", request.getStartTime(), request.getEndTime());

        queryWrapper.groupBy("point_id", "meal_date", "meal_no")
                .orderByAsc("point_id", "meal_date", "meal_no");


        List<EntityXfRecord> entityXfRecords = new ArrayList<>();
        for (String month : dates) {
            QueryWrapper<EntityXfRecord> queryWrapper2 = queryWrapper.clone();
            queryWrapper2.eq("month_value", month);
            List<EntityXfRecord> entityXfRecords3 = xfRecordMapper.selectList(queryWrapper2);
            if (entityXfRecords3 != null && entityXfRecords3.size() > 0) entityXfRecords.addAll(entityXfRecords3);
        }

        Map<String,GetPointMealPreVo> stringGetPointMealPreVoMap =new HashMap<>();

        if (entityXfRecords != null && entityXfRecords.size() > 0) {
            for (EntityXfRecord entityXfRecord : entityXfRecords) {
                String key = "point_id:"+entityXfRecord.getPointId()+"#meal_date:"+entityXfRecord.getMealDate()+ "#meal_no:"+entityXfRecord.getMealNo();
                if(stringGetPointMealPreVoMap.containsKey(key)){
                    GetPointMealPreVo getPointMealPreVo =  stringGetPointMealPreVoMap.get(key);
                    getPointMealPreVo.setDcTimes(entityXfRecord.getAmount1()+getPointMealPreVo.getDcTimes());
                    getPointMealPreVo.setTcTimes(entityXfRecord.getAmount2()+getPointMealPreVo.getTcTimes());
                    getPointMealPreVo.setQcTimes(entityXfRecord.getAmount3()+getPointMealPreVo.getQcTimes());
                    stringGetPointMealPreVoMap.put(key,getPointMealPreVo);
                }else{
                    GetPointMealPreVo getPointMealPreVo = new GetPointMealPreVo();
                    getPointMealPreVo.setMealDate(entityXfRecord.getMealDate());
                    getPointMealPreVo.setMealNo(entityXfRecord.getMealNo());
                    getPointMealPreVo.setPointId(entityXfRecord.getPointId().intValue());
                    getPointMealPreVo.setDcTimes(entityXfRecord.getAmount1());
                    getPointMealPreVo.setTcTimes(entityXfRecord.getAmount2());
                    getPointMealPreVo.setQcTimes(entityXfRecord.getAmount3());
                    stringGetPointMealPreVoMap.put(key,getPointMealPreVo);
                }
            }

            pointMealPreList = new ArrayList(stringGetPointMealPreVoMap.values());
        }

        return pointMealPreList;
    }



    @Override
    public List<GetPointMealSendVo> getPointMealSend(GetPointMealSendReportsRequest request) {

        List<GetPointMealSendVo> pointMealPreList = new ArrayList<>();

        /**
         * select point_id,meal_date,meal_no,receipt_id ,receipt_name
         * 	,sum(case when cons_state in (3,5) then 1  else 0 end ) as  dcTimes
         * 	,sum(case when cons_state=4 then 1  else 0 end ) as  qcTimes
         * 	,sum(case when cons_state=6 then 1  else 0 end ) as  tcTimes
         * 				from xf_record_202406
         * 				where com_id =#{comId}   and cons_state in (3,4,5,6)
         *  if(#{pointId} 不为空)  and point_id = #{pointId}
         *  			and meal_date between #{startTime}  and  #{endTime}
         * if(#{receiptId} 不为空)  and receipt_id = #{receiptId}
         * 	  if(#{receiptName} 不为空)  and receipt_name  like  '%#{receiptName}%'
         * group by  point_id,meal_date,meal_no,receipt_id ,receipt_name
         * order  by  point_id,meal_date,meal_no,receipt_id ,receipt_name
         */


        QueryWrapper<EntityXfRecord> queryWrapper = Wrappers.query();


        String lastMonth = request.getStartTime().substring(0,7).replace("-","");
        if(request.getStartTime().substring(0,7).equals(request.getEndTime().substring(0,7))){
            //同月往前查一个月
            lastMonth = DateUtils.getLastMonth(lastMonth, 1);
        }
        List<String> dates = new ArrayList<>();
        try {
            String start3 = lastMonth;
            String end3 = request.getEndTime().substring(0, 7).replace("-", "");
            dates = DateUtils.getMonthBetween(start3, end3);
        } catch (Exception e) {
            log.info("请求方式:【{}】,查询的请求的数据:【{}】,异常信息:【{}】", "获取消费记录信息", request, e);
        }


        queryWrapper.select("point_id", "meal_date", "meal_no","receipt_id","receipt_name",
                        "sum(case when cons_state in (3,5) and if_refund = 0 then 1  else 0 end ) as  amount1",//dcTimes
                        "sum(case when cons_state=4 then 1  else 0 end ) as  amount3", //qcTimes
                        "sum(case when cons_state=6 then 1  else 0 end ) as  amount2")  //tcTimes
                .eq("com_id", request.getComId())
                .in("cons_state", 3, 4, 5, 6);

        if (request.getPointId() != null) {
            queryWrapper.eq("point_id", request.getPointId());
        }
        if (request.getReceiptId() != null) {
            queryWrapper.eq("receipt_id", request.getReceiptId());
        }

        if (!CommonUtil.isNull(request.getReceiptName())) {
            queryWrapper.like("receipt_name", request.getReceiptName());
        }
        if (request.getMealNo() != null) {
            queryWrapper.eq("meal_no", request.getMealNo());
        }

        queryWrapper.between("meal_date", request.getStartTime(), request.getEndTime());

        queryWrapper.groupBy("point_id", "meal_date", "meal_no","receipt_id","receipt_name")
                .orderByAsc("point_id", "meal_date", "meal_no","receipt_id");


        List<EntityXfRecord> entityXfRecords = new ArrayList<>();
        for (String month : dates) {
            QueryWrapper<EntityXfRecord> queryWrapper2 = queryWrapper.clone();
            queryWrapper2.eq("month_value", month);
            List<EntityXfRecord> entityXfRecords3 = xfRecordMapper.selectList(queryWrapper2);
            if (entityXfRecords3 != null && entityXfRecords3.size() > 0) entityXfRecords.addAll(entityXfRecords3);
        }



        Map<String,GetPointMealSendVo> stringGetPointMealPreVoMap =new HashMap<>();

        if (entityXfRecords != null && entityXfRecords.size() > 0) {
            for (EntityXfRecord entityXfRecord : entityXfRecords) {
                String key = "point_id:"+entityXfRecord.getPointId()+"#meal_date:"+entityXfRecord.getMealDate()+ "#meal_no:"+
                        entityXfRecord.getMealNo() +"receipt_id:"+entityXfRecord.getReceiptId()+"receipt_name:"+entityXfRecord.getReceiptName();
                if(stringGetPointMealPreVoMap.containsKey(key)){
                    GetPointMealSendVo getPointMealPreVo =  stringGetPointMealPreVoMap.get(key);
                    getPointMealPreVo.setDcTimes(entityXfRecord.getAmount1()+getPointMealPreVo.getDcTimes());
                    getPointMealPreVo.setTcTimes(entityXfRecord.getAmount2()+getPointMealPreVo.getTcTimes());
                    getPointMealPreVo.setQcTimes(entityXfRecord.getAmount3()+getPointMealPreVo.getQcTimes());
                    stringGetPointMealPreVoMap.put(key,getPointMealPreVo);
                }else{
                    GetPointMealSendVo getPointMealPreVo = new GetPointMealSendVo();
                    getPointMealPreVo.setMealDate(entityXfRecord.getMealDate());
                    getPointMealPreVo.setMealNo(entityXfRecord.getMealNo());
                    getPointMealPreVo.setPointId(entityXfRecord.getPointId().intValue());
                    getPointMealPreVo.setReceiptId(entityXfRecord.getReceiptId().intValue());
                    getPointMealPreVo.setReceiptName(entityXfRecord.getReceiptName());
                    getPointMealPreVo.setPointId(entityXfRecord.getPointId().intValue());
                    getPointMealPreVo.setDcTimes(entityXfRecord.getAmount1());
                    getPointMealPreVo.setTcTimes(entityXfRecord.getAmount2());
                    getPointMealPreVo.setQcTimes(entityXfRecord.getAmount3());
                    stringGetPointMealPreVoMap.put(key,getPointMealPreVo);
                }
            }
            pointMealPreList = new ArrayList(stringGetPointMealPreVoMap.values());
        }

        return pointMealPreList;
    }


    @Override
    public List<EntityXfRecord> getTimesManJian(EntityXfRecord entityXfRecord) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("discount,count(*) as times");
        queryWrapper.eq("p_id",entityXfRecord.getPId());
        queryWrapper.eq("com_id",entityXfRecord.getComId());
//        queryWrapper.eq("cons_state",1);
        queryWrapper.in("cons_state",1,3,5);
        queryWrapper.eq("pay_state",1);
        queryWrapper.gt("discount",0);
        queryWrapper.eq("if_refund",0);
        queryWrapper.eq("cons_type",entityXfRecord.getConsType());

        if(!CommonUtil.isNull(entityXfRecord.getReserved1()) && entityXfRecord.getReserved1().equals("selectMerchTrue")){
            queryWrapper.eq("merch_id",entityXfRecord.getMerchId());
        }
        if (!CommonUtil.isNull(entityXfRecord.getReserved2())) {
            queryWrapper.apply("find_in_set(  meal_no , \""+entityXfRecord.getReserved2()+"\" )  and meal_no IS NOT NULL ");
        }

        String strDate=DateUtils.getStrDate(entityXfRecord.getConsTime(),DateUtils.YYYY_MM_DD_HH_MM_SS);
        String endMonth = strDate.substring(0,7).replace("-","");
        String startMonth = DateUtils.getLastMonth(endMonth, 1);

        List<String> dates = new ArrayList<>();
        dates.add(endMonth);
        dates.add(startMonth);

        //日期范围查询
//        queryWrapper.and(wrapper -> {
//            for (String info : dates) {
//                wrapper.or().eq("month_value", info);
//            }
//        });

        //餐段按天
        strDate = strDate.substring(0,10);
        queryWrapper.eq( "meal_date",strDate);

        queryWrapper.groupBy("discount");


        List<EntityXfRecord> entityXfRecords = new ArrayList<>();
        for (String month : dates) {
            QueryWrapper<EntityXfRecord> queryWrapper2 = queryWrapper.clone();
            queryWrapper2.eq("month_value", month);
            List<EntityXfRecord> entityXfRecords3 = xfRecordMapper.selectList(queryWrapper2);
            if (entityXfRecords3 != null && entityXfRecords3.size() > 0) entityXfRecords.addAll(entityXfRecords3);
        }


        List<EntityXfRecord> entityXfRecords3 = new ArrayList<>();
        Map<Long,EntityXfRecord> longEntityXfRecordHashMap =new HashMap<>();

        if (entityXfRecords != null && entityXfRecords.size() > 0) {
            for (EntityXfRecord entityXfRecord2 : entityXfRecords) {
                Long key = entityXfRecord2.getDiscount();
                if(longEntityXfRecordHashMap.containsKey(key)){
                    EntityXfRecord entityXfRecord1 =  longEntityXfRecordHashMap.get(key);
                    entityXfRecord1.setTimes(entityXfRecord1.getTimes()+entityXfRecord2.getTimes());
                    longEntityXfRecordHashMap.put(key,entityXfRecord1);
                }else{
                    longEntityXfRecordHashMap.put(key,entityXfRecord2);
                }
            }
            entityXfRecords3 = new ArrayList(longEntityXfRecordHashMap.values());
        }

        return entityXfRecords3;
//        return xfRecordMapper.selectList(queryWrapper);
    }

    @Override
    public EntityXfRecord getOneRecode(EntityXfRecord xfRecord) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper
                .eq(ObjectUtil.isNotEmpty(xfRecord.getComId()),"com_id",xfRecord.getComId())
                .eq(ObjectUtil.isNotEmpty(xfRecord.getPId()),"p_id",xfRecord.getPId())
                .eq(ObjectUtil.isNotEmpty(xfRecord.getDeptId()),"dept_id",xfRecord.getDeptId())
                .eq(ObjectUtil.isNotEmpty(xfRecord.getMealDate()),"meal_date",xfRecord.getMealDate())
                .eq(ObjectUtil.isNotEmpty(xfRecord.getMealNo()),"meal_no",xfRecord.getMealNo())
                .eq("month_value",xfRecord.getMonthValue())
                .last("limit 1")

        ;

        return this.getOne(queryWrapper);
    }

    @Override
    public List<OrderedMealBVo> getUserOrderedB(GetUserOrderedB request) {
        Set<String> months = new HashSet<>();
        months.add(DateUtil.format(DateUtil.offsetMonth(request.getBeginDate(),-1), "yyyyMM"));
        months.add(DateUtil.format(DateUtil.offsetMonth(request.getEndDate(),-1), "yyyyMM"));
        months.add(DateUtil.format(request.getEndDate(), "yyyyMM"));

        List<OrderedMealBVo> list = new ArrayList<>();
        for (String month: months){
            QueryWrapper<EntityXfRecord> recordQueryWrapper = new QueryWrapper<>();
            recordQueryWrapper
                    .select("meal_date,meal_no ,MIN(cons_state) cons_state, plat_order_no  as  plat_order_no")
                    .eq("month_value",month)
                    .eq("com_id",request.getComId())
                    .eq("p_id",request.getPId())
                    .in("cons_state","4","5")
                    .eq("if_refund","0")
                    .between("meal_date",request.getBeginDate(),request.getEndDate())
                    .groupBy("meal_date","meal_no")
                    .orderByAsc("meal_date","meal_no");
            this.list(recordQueryWrapper).forEach(recode->{
                if(ObjectUtil.isNotEmpty(recode.getMealNo()) && !"null".equals(recode.getMealNo())){
                    OrderedMealBVo orderedMealBVo = new OrderedMealBVo();
                    orderedMealBVo.setMealDate(recode.getMealDate());
                    orderedMealBVo.setOrderNo(recode.getPlatOrderNo());
                    orderedMealBVo.setMealNo(Integer.valueOf(recode.getMealNo()));
                    orderedMealBVo.setConsState(recode.getConsState());
                    list.add(orderedMealBVo);
                }

            });
        }
        return list;
    }

    @Override
    public Result getConsPerByMealTimes(GetConsPerByMealTimesReq request) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();

        queryWrapper.eq("com_id",request.getComId());
        queryWrapper.in("cons_state",1,3,5);
        queryWrapper.eq("pay_state",1);
        queryWrapper.eq("if_refund",0);

        queryWrapper.eq("month_value",request.getSummaryDate().replace("-",""));

        if(!CommonUtil.isNull(request.getDeptIds())){
            List<Integer> deptIds = Arrays.asList(request.getDeptIds().split(",")).stream().map(x -> Integer.parseInt(x)).collect(Collectors.toList());
            queryWrapper.in("dept_id",deptIds);
        }

        if(!CommonUtil.isNull(request.getAccTypeIds())){
            List<Integer> accTypeIds = Arrays.asList(request.getAccTypeIds().split(",")).stream().map(x -> Integer.parseInt(x)).collect(Collectors.toList());
            queryWrapper.in("acc_type_id",accTypeIds);
        }

        if(!CommonUtil.isNull(request.getCode())){
            queryWrapper.eq("p_code",request.getCode());
        }

        if(!CommonUtil.isNull(request.getName())){
            queryWrapper.eq("p_name",request.getName());
        }

        if(!CommonUtil.isNull(request.getCardNo())){
            queryWrapper.eq("card_no",request.getCardNo());
        }

        if(!CommonUtil.isNull(request.getMealNo())){
            queryWrapper.eq("meal_no",request.getMealNo());
        }

        QueryWrapper<EntityXfRecord> queryWrapper2 = queryWrapper.clone();
        queryWrapper.orderByAsc("id");
        queryWrapper.select("p_id" ,"p_name","p_code" ,"card_no","count(1) as times","dept_id" , "dept_name");
        queryWrapper.groupBy("p_id");


        Page<EntityXfRecord> page = new Page<>(request.getPage(), request.getSize());
        Page<EntityXfRecord> xfRecordPage =  xfRecordMapper.selectPage(page,queryWrapper);


        Map<String,Object> resMap =new HashMap<>();
        resMap.put("total",xfRecordPage.getTotal());
        resMap.put("list",new ArrayList<>());

        if(!xfRecordPage.getRecords().isEmpty()){

            List<Long> pIds = xfRecordPage.getRecords().stream().map(EntityXfRecord -> EntityXfRecord.getPId()).collect(Collectors.toList());
            queryWrapper2.in("p_id" , pIds);
            queryWrapper.orderByAsc("meal_date", "meal_no");
            queryWrapper2.groupBy("p_id","meal_date" , "meal_no");


            queryWrapper2.select("p_id","p_name" ,"count(1) as times" ,"meal_date","meal_no");
            List<EntityXfRecord> xfRecordPage2 =  xfRecordMapper.selectList(queryWrapper2);

            //返回的list
            JSONArray jsonArray =new JSONArray();

            //餐段 一人一餐一行 三餐一统计&&共四行
            for (EntityXfRecord entityXfRecord:xfRecordPage.getRecords()) {

                //补充一行的数据
                JSONObject jsonObjectOrg = new JSONObject();
                jsonObjectOrg.put("pId", entityXfRecord.getPId());
                jsonObjectOrg.put("pName", entityXfRecord.getPName());
                jsonObjectOrg.put("pCode", entityXfRecord.getPCode());
                jsonObjectOrg.put("deptId", entityXfRecord.getDeptId());
                jsonObjectOrg.put("deptName", entityXfRecord.getDeptName());
                if(entityXfRecord.getDeptId()==0l){
                    jsonObjectOrg.put("deptName", "本单位");
                }
                if(entityXfRecord.getDeptName()==null){
                    jsonObjectOrg.put("deptName", "");
                }
                jsonObjectOrg.put("times", entityXfRecord.getTimes());
                jsonObjectOrg.put("mealName", "合计");
                jsonObjectOrg.put("cardNo", entityXfRecord.getCardNo());
                if(entityXfRecord.getCardNo()==null){
                    jsonObjectOrg.put("cardNo", "");
                }
                for (Integer j= 1;j<=31;j++) {
                    jsonObjectOrg.put(String.valueOf(j), 0);
                }

                //餐段号   餐段 一行的数据
                for (EntityXfMealInfo entityXfMealInfo:request.getMealInfos()){
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("pId", entityXfRecord.getPId());
                    jsonObject.put("pCode", entityXfRecord.getPCode());
                    jsonObject.put("pName", entityXfRecord.getPName());
                    jsonObject.put("deptId", entityXfRecord.getDeptId());
                    jsonObject.put("deptName", entityXfRecord.getDeptName());
                    if(entityXfRecord.getDeptId()==0l){
                        jsonObject.put("deptName", "本单位");
                    }
                    if(entityXfRecord.getDeptName()==null){
                        jsonObject.put("deptName", "");
                    }
                    jsonObject.put("times", 0);
                    jsonObject.put("mealName", entityXfMealInfo.getMealName());
                    jsonObject.put("cardNo", entityXfRecord.getCardNo());
                    if(entityXfRecord.getCardNo()==null){
                        jsonObject.put("cardNo", "");
                    }
                    //补充31列数据 31天
                    for (Integer j= 1;j<=31;j++) {
                        jsonObject.put(String.valueOf(j), 0);
                        for (EntityXfRecord entityXfRecord1:xfRecordPage2){
                            if(entityXfRecord.getPId().equals(entityXfRecord1.getPId())
                                    && entityXfRecord1.getMealNo().equals(entityXfMealInfo.getMealNo()) ){

                                Integer mealDateInt = Integer.valueOf(DateUtils.getStrDate(entityXfRecord1.getMealDate(),"dd"));

                                if(mealDateInt.equals(j)){
                                    jsonObject.put(j.toString() , entityXfRecord1.getTimes());
                                    jsonObject.put("times", jsonObject.getIntValue("times")+entityXfRecord1.getTimes());

                                    jsonObjectOrg.put(j.toString(), jsonObjectOrg.getIntValue(j.toString())+entityXfRecord1.getTimes());

                                    break;
                                }
                            }
                        }
                    }
                    //加一行
                    jsonArray.add(jsonObject);
                }

                if(request.getTotalPer()==1){
                    //加一行
                    jsonArray.add(jsonObjectOrg);
                }
            }
            resMap.put("list",jsonArray);
        }

        return ResultUtil.success(resMap);
    }

    @Override
    public Result getConsDeptByMealTimes(GetConsDeptByMealTimesReq request) {
        QueryWrapper<EntityXfRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("dept_id","acc_type_id","dept_name" ,"count(1) as times" ,"count(distinct p_id,meal_date,meal_no)  as times1 ");

        queryWrapper.eq("com_id",request.getComId());
        queryWrapper.in("cons_state",1,3,5);
        queryWrapper.eq("pay_state",1);
        queryWrapper.eq("if_refund",0);

        if(!CommonUtil.isNull(request.getDeptIds())){
            List<Integer> deptIds = Arrays.asList(request.getDeptIds().split(",")).stream().map(x -> Integer.parseInt(x)).collect(Collectors.toList());
            queryWrapper.in("dept_id",deptIds);
        }

        if(!CommonUtil.isNull(request.getAccTypeIds())){
            List<Integer> accTypeIds = Arrays.asList(request.getAccTypeIds().split(",")).stream().map(x -> Integer.parseInt(x)).collect(Collectors.toList());
            queryWrapper.in("acc_type_id",accTypeIds);
        }

        String month = request.getStartDate().substring(0,7).replace("-","");
        queryWrapper.eq("month_value",month);

        queryWrapper.ge("meal_date",request.getStartDate());
        queryWrapper.le("meal_date",request.getEndDate());

        queryWrapper.groupBy("dept_id","acc_type_id");
        queryWrapper.orderByAsc("dept_name","acc_type_id");

        Page<EntityXfRecord> page = new Page<>(request.getPage(), request.getSize());
        Page<EntityXfRecord> xfRecordPage =  xfRecordMapper.selectPage(page,queryWrapper);


        Map<String,Object> resMap =new HashMap<>();
        resMap.put("total",xfRecordPage.getTotal());
        resMap.put("list",new ArrayList<>());

        if(!xfRecordPage.getRecords().isEmpty()){
            //返回的list
            JSONArray jsonArray =new JSONArray();

            for (EntityXfRecord entityXfRecord:xfRecordPage.getRecords()) {
                /**
                 * deptName	部门	string
                 * accTypeId	账户类型id	string
                 * alltimes 	总刷脸次数	int
                 * times	有效次数	int
                 */
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("deptName",entityXfRecord.getDeptName());
                jsonObject.put("accTypeId",entityXfRecord.getAccTypeId());
                jsonObject.put("deptId",entityXfRecord.getDeptId());
                jsonObject.put("alltimes",entityXfRecord.getTimes());
                jsonObject.put("times",entityXfRecord.getTimes1());

                jsonArray.add(jsonObject);
            }

            resMap.put("list",jsonArray);
        }

        return ResultUtil.success(resMap);
    }
}
